3

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.

Edited

And also able to get books from category CA only. And how to include query criteria such as .where("book.p_year = 2012")

Yakob Ubaidi
  • 1,846
  • 2
  • 20
  • 23
  • maybe `.where("category_id = ? AND category_id = ?",1,2)` – Thanh Nov 17 '12 at 15:07
  • Kien, I guess that wont work because, it's like the query were executed for each row with that criteria. eg: category_id is 1 but u query for 1 and 2. Surely nothing will match. ps: I already tested it. – Yakob Ubaidi Nov 21 '12 at 16:51

1 Answers1

3
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):

SELECT book_id, count(*) as c from books_categories where category_id IN (1,2) group by book_id having count(*) >= 2;
Philip Hallstrom
  • 19,673
  • 2
  • 42
  • 46