Select records from table where column value is contained in string (mysql with ActiveRecord)-CodePudding

Say I have a table called books with a column in it called keyword. Say I also have a string called words.

I want to select all records from books where the column keyword is included in words. I can do this using LIKE directly in mysql. I'm trying to translate this into an ActiveRecord query for a rails app if anyone could give me a hand with the syntax.

The tricky part is that I want to find records where the column value is included in my string, not the other way around. Very similar to this question, except I need to translate it to ActiveRecord. SQL - Query to find if a string contains part of the value in Column

Here is a working sql query that I am trying to translate.

SELECT * FROM books WHERE "new science fiction" LIKE CONCAT('%',keyword,'%');

In the above example "new science fiction" is the string words.

Say I have 3 book records. book1 has keyword 'science', book2 has keyword 'fiction' and book3 has keyword 'other'. My above query would return the records book1 and book2. Because 'science' and 'fiction' are included in the words string 'new science fiction'.

My SQL query works but I can't figure out how to do it with a Book.where statement in ActiveRecord.

CodePudding user response：

I think your best option is:

Book.where(keyword: words.split)

which will result in

SELECT * FROM books WHERE books.keyword IN ('new', 'science', 'fiction')

this will return the same records.

Note: Depending on your RDBMS IN() may be case sensitive. To avoid this we can change the above to

Book.where(Book.arel_table[:keyword].lower.in(words.downcase.split))

which will result in

SELECT * FROM books WHERE LOWER(books.keyword) IN ('new', 'science', 'fiction')

If you really want to go the with the way you have it now we can hack this together as follows:

Book.where(
  Arel::Nodes::UnaryOperation.new(nil,Arel::Nodes.build_quoted(words))
    .matches(
      Arel::Nodes::NamedFunction.new(
       'CONCAT', 
       [Arel.sql("'%'"),Book.arel_table[:keyword],Arel.sql("'%'")])
))

This will result in the desired SQL while still taking advantage of the escaping provided by Arel and the connection adapter.

Case sensitivity is not an issue here because Arel::Predications#matches uses case_sensitive = false by default meaning Postgres will use ILIKE instead of LIKE