0

Using this :

.. .where("field1 IN(?) AND field2 LIKE(?) AND field3 LIKE(?) ", params[:array_for_field1], "%#{params[:array_for_field2]}%", "%#{params[:array_for_field3]}%")

I'm getting this :

SELECT `table`.* FROM `table` WHERE(field1 IN('value1','value2','value3') AND field2 LIKE('%[\"value1\", \"value2\"]%') AND field3 LIKE('%[\"value1\"]%') )

I would like to know where's the mistake, for field1 works, but the "LIKE Query" part using % is not, the params comes from a checkboxes form. thank you

user1936635
  • 45
  • 6
  • Use this response : https://stackoverflow.com/questions/19412607/rails-where-like-and-array – Aschen Jun 20 '17 at 14:36
  • @user1936635 take a look at this answer I posted yesterday https://stackoverflow.com/questions/44633250/ruby-rails-sql-query-reordering-words-in-a-search-term/44634062#44634062. This is cross database compatible using `Arel`. You will just have to expand it ever so slightly to handle the multiple fields you are interested in – engineersmnky Jun 20 '17 at 16:18
  • awesome, I'll try Arel. thank you – user1936635 Jun 20 '17 at 16:38
  • Possible duplicate of: https://stackoverflow.com/questions/26094430/safe-activerecord-like-query – spickermann Jun 20 '17 at 19:22

2 Answers2

1

I end up using this solution with REGEX, it works as I needed.

.where("field1 IN(?) AND field2 RLIKE(?) AND field3 RLIKE(?) ", params[:array_for_field1], params[:array_for_field2].join("|"), params[:array_for_field3].join("|"))
user1936635
  • 45
  • 6
0

According to this response if you are using Postgres you can try something like this : 

field2_ilike_params = params[:array_for_field2].map { |p| "%p%" }
field3_ilike_params = params[:array_for_field3].map { |p| "%p%" }
Model.where(field1: params[:array_for_field1]).where("field2 ILIKE ANY ( array[?] ) AND field3 ILIKE ANY ( array[?] )", field2_ilike_params, field3_ilike_params)
Aschen
  • 1,691
  • 11
  • 15