How can I do something like this?
myarray = ["name1","name2"]
Product.where('name ILIKE ?', %#{myarray}%)
I need to get all products where names are like name1 and name2.
Is this possible?
Add a comment
|
3 Answers
I think You want to test all values with ILIKE function.
This is how it's done in Postgres:
select * from table where value ilike any (array['%foo%', '%bar%', '%baz%']);
Try to convert to Rails/Ruby syntax like this:
myarray_with_percetage_signs = ["name1","name2"].map {|val| "%#{val}%" }
Product.where("name ILIKE ANY ( array[?] )", myarray_with_percetage_signs)
Comments
Recently encountered the same issue, and, since neither answer helped me (I'm using a MySQL database), I thought I would share my solutions.
If you have a small database and don't mind retrieving all products, you could do the filtering after retrieving all products from the database using the following:
Product.select { |product| myarray.any? { |str| product.name.include? str } }If, however, you prefer to perform the query at the database level, you could use the
WHERE REGEXPclause by converting the array to a pipe-separated list first:Product.where("`name` REGEXP '#{Regexp.new(myarray.join('|'))}'")
2 Comments
Pete
Careful with the recommendation in the second bullet: the SQL is unsanitized. This can result in SQL injection. In particular, you want to avoid
where("id = #{some_var}") and use this syntax instead: where("id = ?", some_var). This allows ActiveRecord to sanitize the string, potentially via Prepared Statements.
Vadim Kononov
Good catch, @Pete. I've updated my post to sanitize input. In my case, I didn't worry about SQL injections, because I wasn't working with user data, but with a constant pre-determined list, but this is definitely important if working with user-input data.
Product.where('name IN (?)', myarray)
should do the trick ;)
1 Comment
Pete
While this will perform an exact match, it does not answer the question. There does not match with SQL
LIKE/ILIKE operator.