Rails where LIKE and array

Question

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?

Answer 1

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)

Answer 2

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 REGEXP clause by converting the array to a pipe-separated list first:

  Product.where("`name` REGEXP '#{Regexp.new(myarray.join('|'))}'")