'Passing regexp as a query to postgresql database instead of array of options in rails

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"



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source