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%')
You'd use the syntax:
Question.where("content LIKE ?" , "%#{farming}%")
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.
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%' }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With