Let say a book model HABTM categories, for an example book A has categories "CA" & "CB".
How can i retrieve book A if I query using "CA" & "CB" only. I know about the .where("category_id in (1,2)") but it uses OR operation. I need something like AND operation.
假设一个图书模型HABTM类别，例如，图书a有类别“CA”和“CB”。如果我只使用“CA”和“CB”查询，如何检索图书A。我知道。where(1,2中的category_id)但是它使用或操作。我需要一些类似的操作。
Edited
编辑
And also able to get books from category CA only. And how to include query criteria such as .where("book.p_year = 2012")
也能从类别CA获得书籍。以及如何包含查询条件，如.where(“book”)。p_year = 2012”)
1 个解决方案
#1
2
ca = Category.find_by_name('CA')
cb = Category.find_by_name('CB')
Book.where(:id => (ca.book_ids & cb.book_ids)) # & returns elements common to both arrays.
Otherwise you'd need to abuse the join table directly in SQL, group the results by book_id, count them, and only return rows where the count is at least equal to the number of categories... something like this (but I'm sure it's wrong so double check the syntax if you go this route. Also not sure it would be any faster than the above):
否则，您需要直接在SQL中滥用联接表，将结果按book_id分组，并计算它们，并且只返回计数至少等于类别数量的行。类似这样的东西(但我确信它是错误的，所以如果你走这条路，请再次检查语法。也不确定它会比上面更快):
SELECT book_id, count(*) as c from books_categories where category_id IN (1,2) group by book_id having count(*) >= 2;
#1
2
ca = Category.find_by_name('CA')
cb = Category.find_by_name('CB')
Book.where(:id => (ca.book_ids & cb.book_ids)) # & returns elements common to both arrays.
Otherwise you'd need to abuse the join table directly in SQL, group the results by book_id, count them, and only return rows where the count is at least equal to the number of categories... something like this (but I'm sure it's wrong so double check the syntax if you go this route. Also not sure it would be any faster than the above):
否则，您需要直接在SQL中滥用联接表，将结果按book_id分组，并计算它们，并且只返回计数至少等于类别数量的行。类似这样的东西(但我确信它是错误的，所以如果你走这条路，请再次检查语法。也不确定它会比上面更快):
SELECT book_id, count(*) as c from books_categories where category_id IN (1,2) group by book_id having count(*) >= 2;