CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
newbietech
newbietech

Reputation: 31

Use Rails 'contains operator' (or alternative) for search with multiple values

I have two models: Author and Book, where

class Book< ApplicationRecord
 belongs_to :author
end
class Author< ApplicationRecord
  has_many :books
 end

Author has a name:string

Book has title:string and author_id as index

I'm interested in finding all authors that have books that contain some given strings

eg if the terms are ['wizard', 'one hundred', 'two cities'] I want to return the authors whose books contain the terms in the title ( one book title containing 'wizard' AND one book containing 'one hundred' AND...)

I could go with something like

class Post
 scope :including_all_title, -> (titles) { where(matching_tag_query(titles, 'AND')) }

 private

  def matching_tag_query(titles, condition_separator = 'AND')
    titles.map { |t| "(title LIKE '%#{t}%')" }.join(" #{condition_separator} ")
 end
 end

( Author.joins(:books).merge(Books.including_all_title... ) but doesn't seem great as the number of terms increases.

I've seen responses that contain Postgres's '@> ' ( eg Postgres: check if array field contains value?)

but so far I couldn't make it work( probably syntax isn't good, I don't get an error but no results )

I'm wondering how could I achieve that with Active Record ( or another solution better than my own)

Edit: I realise I might have been unclear, what I'm trying to do is: If Author(name:'John') has books Book(title: 'The wonderful Wizard of Oz') Book(title: 'Fantastic beasts') Book(title: 'Some other work') Book(title: 'Another book') and I query by ["%wizard%", "%fantastic%"] I would want to find the author whose books , match ALL of the keywords, so in this case John would be a match( because "wizard" matches one book, and "fantastic" matches another, even if some are unmatched- I'm interested in my keywords)

Upvotes: 3

Views: 1607

Answers (1)

mu is too short
mu is too short

Reputation: 434645

This should work:

def matching_tag_query(titles, condition_separator = 'AND')
  titles.map { |t| "(title LIKE '%#{t}%')" }.join(" #{condition_separator} ")
end

but it is subject to SQL injection and you can make it cleaner using PostgreSQL's expr op any(array) and expr op all(array) with the ILIKE operator. Something more like this:

scope :including_all_title, -> (titles) { where('title ilike all(array[?])', titles.map { |t| "%#{sanitize_sql_like(t)}%" })
scope :including_any_title, -> (titles) { where('title ilike any(array[?])', titles.map { |t| "%#{sanitize_sql_like(t)}%" })

Should be fine for realistic titles but if someone throws a couple thousand titles at you then it could run into query size limits and it wouldn't find anything anyway.

In response to the comments: If you want to find all the books that match any of the keywords then:

books = Book.where('title like any(array[?])', titles.map { |t| "%#{sanitize_sql_likt(t)}%" })

Then to find the authors:

Author.where(id: books.select(:author_id))

That'll do a subquery like this:

select *
from authors
where id in (select author_id from books where ...)

which won't be as efficient as it could be but should be good enough. If it is too slow, convert it to a JOIN.

Upvotes: 1

Related Questions