Book has_and_belong_to_many Category.
In other words, a category may have a lot of books, and a book can
have more than one category.
What is the best practice to query all books related to some specified
categories.
For example, if I have following books
"From Java To Ruby" - Category: Java, Ruby
"Ruby Programming" - Category: Ruby
"Inside Python" - Category: Python
If I search Java & Python, I got "From Java To Ruby" and "Inside
Python".
My goal is use a single query join to fetch the results.
It's possible to use find_by_sql, but I wonder if is there any better
way?
I think the best way to implement HABTM is using midle model, it will useful for your query performance
Example
Model Book
has_many :book_category_relations
has_many :categories, :through => :book_category_relation
end
Model Category
has_many :book_category_relations
has_many :books, :through => :book_category_relations
end
Model BookCategoryRelation
belongs_to :book
belongs_to :category
end
So if you want search book with some conditions will be:
@books = Book.all(:joins => “INNER JOIN book_category_relations ON books.id = book_category_relations.book_id
INNER JOIN categories ON book_category_relations.category_id = categories.id”,
:conditions => [“categories.name LIKE ?”, “%#{your_filter}%”])