user1936635 user1936635 - 4 days ago 4
MySQL Question

LIKE Query in rails 5 not working

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

Answer

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)