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

Question

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.

engineersmnky · Accepted Answer · 2023-01-14T03:31:38.347

1

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

edited Jan 14 '23 at 03:31

answered Jan 13 '23 at 20:16

engineersmnky

25,495
2
36
52

Hey thanks, that works! Just a couple questions. – dthegnome Jan 14 '23 at 18:05
In response to using `Book.where(keyword: words.split)` that would be great, but the string that I will actually be searching through is a url and I can't count on any particular special characters. For example searching "thing.com/mens/shoes?deal=freeshipping" for the word stored on my db record "deal". But Arel worked. Just wondering if `Arel::Nodes::UnaryOperation.new(nil,Arel::Nodes.build_quoted(words))` is all necessary where I'm actually using a url instead of my `words` example. Is that for escaping special characters? Is it preventing sql injection? Do I need to worry about that? – dthegnome Jan 14 '23 at 18:19
And I accepted your answer, thanks again! Just wanted to follow up to see if there is any way to clean it up or if there is anything extra I need to do for security. – dthegnome Jan 14 '23 at 18:21
1

@dthegnome it will perform escaping and help prevent injection. As a simple example imagine your URL contains double hypens. – engineersmnky Jan 15 '23 at 00:26
That's great, thank you - glad I don't have worry about sql injection this way. But do you think you could just explain in a bit more detail what the first half is doing to the input string please? This part: `Arel::Nodes::UnaryOperation.new(nil,Arel::Nodes.build_quoted(words))` I've been reading some Arel primers and documentation but haven't found much info about these methods yet. I get how we are basically saying "Books where [string] matches %book.keyword%" but I'm a little unclear on what the `UnaryOperation` object is and what the `build_quoted` method is. Thanks again! – dthegnome Jan 15 '23 at 05:49
1

@dthegnome `build_quoted` is what handles the escaping. I explained the steps taken in [this post](https://stackoverflow.com/questions/73378366/is-arelnodesquoted-the-best-way-to-avoid-injection-when-using-namedfuncti). `UnaryOperation` is just a hack. `Quoted` does not have prediction methods e.g. `matches` but a `UnaryOperation` does. `UnaryOperation` takes 2 arguments the operator and the operand the the are formatted as `"[operator] [operand]" so we just used `nil` as the operator to obtain an object that we can call predications on. – engineersmnky Jan 15 '23 at 14:50
Hey sorry to bug you again, but the table I'm querying for is actually joined to another table that I am querying. Any chance you could give me a hand with that syntax too? So imagine and the Book belongs to a table Author. I actually want to get Authors that have Books whose keyword is contained in the given string. `Author.left_outer_joins(:books).where( ... url contains book.keyword ... )` – dthegnome Jan 16 '23 at 18:35
1

@dthegnome it would be the same thing. `Author.joins(:books).where([INSERT AREL CLAUSE HERE])`. You might need to add `distinct` as well to avoid getting duplicate `Authors`. A left join is pointless since you are applying a condition to "books", which just creates an INNER JOIN anyway. – engineersmnky Jan 16 '23 at 18:45
Ah yup got it. Thanks again. I was making it more complicated than it needed to be. I was trying to do `joins(:books).where( { books: [AREL CLAUSE] } )` which you have to do for regular activerecord joins. Turns out as you said you just directly add the clause into the where. And thanks for the hint about distinct too, I'll keep that in mind. You really helped clarify this for me, very much appreciated. – dthegnome Jan 16 '23 at 18:50

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

1 Answers1