I'm trying to search where one column could equal one value and the other could have multiple values...
This works, with one value for each...
<%= Post.where("category LIKE ? OR commentary LIKE?", 20, "%mom%").count %>
This does not work...
<%= Post.where("category LIKE ? OR commentary LIKE?", 20, [{"%mom%", "%mother%"}]).count %>
How would be the best way to solve this in rails?
Thank you.
What you are trying to do is only work with IN
You can do as follow;
<%= Post.where("category LIKE ? OR commentary LIKE? OR commentary LIKE?", 20, "%mom%", "%mother%"]).count %>
This will generate the following sql query;
SELECT Count(*) FROM `posts` WHERE (category like "20" OR commentary like "%mom%" OR commentary like "%mother%" )
You could add it as a scope to move the repetition to your Post class:
class Post < ActiveRecord::Base
scope :categories_like, ->(to_match) {
where(
[to_match.map("category like ?").join(" OR ")] +
(to_match.map { |m| "%#{m}%" })}
The scope adds as many category like ? clauses as there are categories to match and then adds each thing you want to match with % prefixed and suffixed. More details on adding scopes like there can be found in the rails guide. (I haven't tested the code as I'm not on my dev machine so it might need a little nudge in the right direction).
You can then use it like:
Post.categories_like(['mom', 'mother', 'madre']).count
As you've pointed out elsewhere it isn't the most efficient code in the whole world but then what you're asking it to isn't particularly efficient in SQL terms either.
