I'm trying to search for unique models with the association where the attribute 'description' of the associated model includes a word from a search query. I would like to find models only with an exact match e.g. if a user is searching for 'egg', I would like the search to include models with 'description' including e.g.: 'egg', 'eggs', 'egg,', 'eggs,', 'Egg' etc. but I don't want the search to include a model with 'eggplant' within its description attribute. Therefore I am providing an array of options so the search will pick up everything that is an exact match and it is at the beginning, the middle and the end of the description and also, if the word from the query exists in plural version within the description or it is followed by a comma.
options = [
"#{query} %",
"#{query}s %",
"#{query}s,%",
"#{query},%",
"% #{query} %",
"% #{query}s %",
"% #{query}s,%",
"% #{query},%",
"% #{query}s",
"% #{query}"
]
@response ||= Model.joins(:associated_models).where(AssociatedModel.arel_table[:name].matches_any(options)).distinct
The above works, however, I was wondering if there is a better way perhaps by passing a RegExp into the query instead of the array of options e.g.:
EDIT
# query is either at the beginning of the description or is following the white space or it is followed by the white space or comma or an 's'
regexp_query = /(^|\s)#{query}($|\s|,)/
@response ||= Model.joins(:associated_models).where(AssociatedModel.arel_table[:name].matches(regexp_query)).distinct
or
@response ||= Model.joins(:associated_models).where('associated_models.name ILIKE ?', regexp_name).distinct
I have tried to use 'REGEXP' or '~*' instead of 'ILIKE' but getting errors like -can't quote Regexp or -PG::SyntaxError: ERROR: syntax error at or near "REGEXP"
I encountered a similar error and in my case, it was resolved by using Regexp#source to get the String expected by ActiveRecord::QueryMethods#where:
>> User.where("name ~ ?", /foo/)
TypeError: can't quote Regexp
>> User.where("name ~ ?", (/foo/).source)
=> []