Search HABTM relationship in ActiveRecord

rubyonrails-talk
1

Consider the following scenario:

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?

2

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}%”])

3

Most of the time, I don’t use habtm because I want complete control on the middle model.

So if you’re following Hendra’s models, to search for a book under a category

cat = [‘Java’, ‘Python’]

books = Book.where(:categories => {:name => cat}).includes(:categories).all

This is assuming that a category has a name attribute and it’s the attribute you use for the category name. haha! that’s confusing.

good luck!