One to Many Query with Joins

Question

I have a book model and a tag model and a join model. The book model has many tags through the join model. How do find books that have both tag 'A' AND tag 'B'?

This works for Just A:

Book.all(:joins => 'tags', :conditions => {'tags.name' => 'A'})

This works for A or B (or seems to):

Book.all(:joins => 'tags', :conditions => {'tags.name' => ['A','B']})

But I'd like to find all the books with A and B.

Answer 1

Here is one approach: find all the books and get rid of the books that do not have tag A and B

Book.all - Book.all(:joins=>'tags', :conditions=>[tags.name <> 'A' and tags.name <> 'B']

Answer 2

This should do it:

Book.all(:joins => 'tags', :conditions => "tags.name = 'A' and tags.name = 'B'")

Answer 3

It won't work this way since the condition is applied to each individual SQL result. Each tag is one result. None will match the AND clause.

I use the ez_where plugin for this sort of stuff:

http://brainspl.at/articles/2006/01/30/i-have-been-busy

Using that, it's simple:

Book.ez_find(:all, :include => :tags) do |book,tag|
  tag.any do 
    tag.name == 'A'
    tag.name == 'B'
  end
end

Answer 4

The only way I can think of to do this is using multiple joins on the join table, one for each tag that you want to AND together. This doesn't scale very well, but works fine for two. The query you want, assuming your join model is called taggings with a foreign key book_id:

SELECT DISTINCT books.* FROM books 
  INNER JOIN taggings t1 ON t1.book_id = book.id 
  INNER JOIN taggings t2 ON t2.book_id = book.id 
WHERE t1.id = (SELECT id FROM tags WHERE name = 'A') 
  AND t2.id = (SELECT id FROM tags WHERE name = 'B')

You can try to use this with the find method, good luck :) Probably easier to just use find_by_sql, like this:

Book.find_by_sql(["SELECT DISTINCT books.* FROM books 
      INNER JOIN taggings t1 ON t1.book_id = book.id 
      INNER JOIN taggings t2 ON t2.book_id = book.id 
    WHERE t1.id = (SELECT id FROM tags WHERE name = ?) 
      AND t2.id = (SELECT id FROM tags WHERE name = ?)", 'A', 'B')

This assumes that tags.name is unique.