一对多查询加入

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'?

我有书模型,标签模型和连接模型。书籍模型通过连接模型有许多标签。如何查找同时标记为“A”和标记“B”的书籍?

This works for Just A:

这适用于Just A:

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

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

这适用于A或B(或似乎):

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

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

但我想找到A和B的所有书籍。

3 个解决方案

#1

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

这是一种方法:查找所有书籍并删除没有标签A和B的书籍

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

#2

This should do it:

这应该这样做:

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

#3

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:

我能想到的唯一方法就是在连接表上使用多个连接,每个连接对应一个AND标记。这不能很好地扩展,但适用于两个。您想要的查询,假设您的连接模型称为带有外键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:

您可以尝试使用find方法,祝你好运:)可能更容易使用find_by_sql,如下所示:

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.

这假设tags.name是唯一的。

#1