Rails- Dynamic search on multiple fields

Question

I'm trying to write a concern to allow search on multiple fields from different models. I want to insert a search_fields array and generate a where OR query with the params[:query] value like this :

scope = scope.where("LOWER(user_name) LIKE LOWER(?)", "%#{params[:query]}%")

it works fine for one value but I need to receive an array of column names (different for different models) and generate a where or query like this (exemple with an array of two items) :

search_fields = ["title", "user_name"]

where("LOWER(title) LIKE LOWER(?) OR LOWER(user_name) LIKE LOWER(?)", "%#{params[:query]}%", "%#{params[:query]}%")

I generate the query like this :

query = search_fields.map do |s|
  "LOWER(#{s}) LIKE LOWER(?)"
end.join(' OR ')

and it seems that there is no where_or method implemented. I could not do it by any means, is there any ideas how it is possible to do ?

Thanks for all answers

Hi @lilith-m, Why could you not to it (generating query) by any means? Could you tell me the issue? — fongfan999, Mar 21 '18 at 13:31
From what I've understood you want to make a SELECT query from more than one table that use LIKE but *different for different models* Is it possible even in sql syntax to retrieve records from different models without joining the tables? I think if columns are on the same table it's going to work but it's going to throw and column is not defined(not exists) exception from the database if your only using query like above without joining.If you want to do this try something like [this](https://stackoverflow.com/questions/12095109/select-from-multiple-tables-without-a-join), hope it helps. — Amr Adel, Mar 21 '18 at 13:36
rails 5 supports [or](https://stackoverflow.com/questions/10871131/how-to-use-or-condition-in-activerecord-query) — Amr Adel, Mar 21 '18 at 13:38
I'm sorry my question is not correctly formed (my english is so bad) I shall use the helper to call from multiple models, so it shall be dynamic. I shall get an array of column names of a table [col1, col2] and generate a where line like this exemple: `where("LOWER(col1) LIKE LOWER(?) OR LOWER(col2) LIKE LOWER(?)", "%#{params[:query]}%", "%#{params[:query]}%")` With Rails5 or method I can do this(it works) `@scope = @scope.where("LOWER(col1) LIKE LOWER(?)", "%#{params[:query]}%").or(@scope.where("LOWER(col2) LIKE LOWER(?)", "%#{params[:query]}%"))` but how do this dynamically ? — lilux_m, Mar 21 '18 at 14:00

score 0 · Accepted Answer · answered Mar 21 '18 at 15:37

Found it !

search_fields = ["title", "user_name"]

query_param = sprintf("%%%s%%", params[:query])
search = search_fields.map do |field|
  query_string = "LOWER(#{field}) LIKE LOWER(?)"
end.join(' OR ')
@scope = @scope.where(search, *[query_param] * search_fields.count)

It generates the following where string :

WHERE (LOWER(title) LIKE LOWER('%searchquery%') OR LOWER(user_name) LIKE LOWER('%searchquery%'))

Rails- Dynamic search on multiple fields

1 Answers1