Rails: Check if associations exist in has_many

Question

Is it possible to check if multiple associations exist in has_many? In this case I want to check if there is a book which has three tags "a", "b" and "c".

(Book and Tag are associated via many to many.)

if Book.all.tags.find("a", "b", "c").any?
  # Yay!
  # There is at least one book with all three tags (a, b & c)
end

Thank you very much. I found a solution here: [http://stackoverflow.com/questions/5835696/rails-has-many-through-associations-find-with-and-condition-not-or-conditio][1] [1]: http://stackoverflow.com/questions/5835696/rails-has-many-through-associations-find-with-and-condition-not-or-conditio — Railsana, May 24 '12 at 19:10

coreyward · Answer 1 · 2012-05-24T00:54:01.223

4

I think this will do the trick (works on has_many):

Book.includes(:tags).where(tags: { name: ['a', 'b', 'c'] }).any?

Breaking it down:

The includes tells Rails to use eager loading to load tags, versus loading them one at a time. This also causes it to pay attention to tags in the queries and generate smarter queries.

The where condition is pretty simple, and passing the array just converts an = comparison into an IN ('a', 'b', 'c') condition.

Rails is smart about any?, so rather than loading the records (which generates a garish query, btw) it generates one that just loads the count.

edited May 24 '12 at 00:54

answered May 24 '12 at 00:41

coreyward

77,547
20
137
166

1

Isn't that going to return all books that have *any* of the three tags, not *all*? – spike May 24 '12 at 03:57
Thank you for the nice approach! As *spike* mentioned, with this query **any** of these tags is required, not **all**. Any ideas how to solve this? – Railsana May 24 '12 at 07:33

score 2 · Accepted Answer · answered Jul 25 '13 at 14:36

2

I use this query structure:

Book
  .select('distinct books.*')
  .joins(:tags)
  .where('tags.slug' => ['a', 'b', 'c'])
  .group("pages.id")
  .having("count(*) = #{['a', 'b', 'c'].size}")

answered Jul 25 '13 at 14:36

Railsana

1,813
2
21
30

score 1 · Answer 3 · answered May 24 '12 at 14:13

I've never found an elegant rails way to solve this problem, so hopefully someone else comes along with a better answer.

The brutish method is to drop down to sql. I'm writing this out of memory, so there may be syntax problems.

Something like this in the Book model. The shortnames for the tables are so they don't conflict with the parent query.

def self.with_tag(tag)
    where("EXISTS (SELECT 1 from books_tags bt, tags t where bt.tag_id = t.id 
    and bt.book_id = books.id and t.name = ?)", tag)
end

Then you would call Book.with_tag('a').with_tag('b').with_tag('c') or define another method. Not sure that the scope variable is needed.

def self.with_all_tags(tags) 
    scope = self.scoped
    tags.each do |t|
        scope = scope.with_tag(t)
    end
end

Rails: Check if associations exist in has_many

3 Answers3