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)
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.