34

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?

Martin
  • 11,216
  • 23
  • 83
  • 140

3 Answers3

58

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)
Edgars Jekabsons
  • 2,833
  • 15
  • 20
1

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('|'))}'")
Vadim
  • 1,916
  • 2
  • 19
  • 39
  • 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. – Pete Jun 11 '18 at 16:50
  • 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. – Vadim Jun 11 '18 at 21:26
-9
Product.where('name IN (?)', myarray)

should do the trick ;)

RonaldOnRails
  • 41
  • 2
  • 2
    While this will perform an exact match, it does not answer the question. There does not match with SQL `LIKE`/`ILIKE` operator. – Pete Jun 11 '18 at 16:48