Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the best way to include a LIKE clause in a Rails query?

What's the best way to include a LIKE clause in a Rails query i.e. something along the lines of (the completely incorrect):

 Question.where(:content => 'LIKE %farming%')
like image 381
Peter Nixey Avatar asked Aug 13 '11 14:08

Peter Nixey


3 Answers

You'd use the syntax:

Question.where("content LIKE ?" , "%#{farming}%")
like image 153
Ammar Avatar answered Nov 12 '22 15:11

Ammar


If this is Rails 3 you can use Arel's matches. This has the advantage of being database agnostic. For example:

Question.where(Question.arel_table[:content].matches("%#{string}%"))

This is somewhat clunky, but easily extracted to scopes, e.g.:

class Question

  def self.match_scope_condition(col, query)
    arel_table[col].matches("%#{query}%")
  end

  scope :matching, lambda {|*args|
    col, opts = args.shift, args.extract_options!
    op = opts[:operator] || :or
    where args.flatten.map {|query| match_scope_condition(col, query) }.inject(&op)
  }

  scope :matching_content, lambda {|*query|
    matching(:content, *query)
  }
end

Question.matching_content('farming', 'dancing') # farming or dancing
Question.matching_content('farming', 'dancing', :operator => :and) # farming and dancing
Question.matching(:other_column, 'farming', 'dancing') # same thing for a different col

Of course to join with "AND" you could just chain the scopes.

Edit: +1 to metawhere and squeel though (haven't tried latter but it looks cool) They both add this type of functionality and much more.

like image 34
numbers1311407 Avatar answered Nov 12 '22 17:11

numbers1311407


If you want really sexy conditions and and don't have problems with external dependencies, I highly recommend MetaWhere and it's successor Squeel:

# MetaWhere
Question.where(:content.like => '%farming%')

# MetaWhere with operators overloaded
Question.where(:content =~ '%farming%')

# Squeel
Question.where { :content.matches => '%farming%' }

# Squeel with operators overloaded
Question.where { :content =~ '%farming%' }
like image 9
Mario Uher Avatar answered Nov 12 '22 16:11

Mario Uher