Rails habtm joins

Question

I have this relationship between categories, products & brands:

class Brand < ActiveRecord::Base
  has_many :products
end

class Category < ActiveRecord::Base
  has_and_belongs_to_many :products
end

class Product < ActiveRecord::Base
  has_and_belongs_to_many :categories
  belongs_to :brand
end

How can I select all categories by specified brand with this relations? I try this but get an error

b = Brand.find(1)
Category.joins(:products).where(:products => b.products)

Answer 1

You did the right thing with the join, just add a more complex where definition:

Category.joins(:products).where(:products => {:brand_id => 1})

Answer 2

Controversially HABTM's are rarely, if ever, a good design and IMO just about the only thing Rails got wrong.

Introduce an xref table to join products and categories and use has_many :through on both sides of the relationship so you end up with

class Brand < ActiveRecord::Base
  has_many :products
  has_many categories :through => products # This is now allowed in Rails 3.x and above
end

class Category < ActiveRecord::Base
  belongs_to :product_category
  has_many :products :through => product_category 
end

class Product < ActiveRecord::Base
  belongs_to :brand
  belongs_to :product_category
  has_many :categories :through => product_category
end

class ProductCategory < ActiveRecord::Base
  has_many :products
  has_many :categories
end

This gives you the best flexibility with the least amount of code re-factoring for you plus a much more intuitive path to get whatever data you need on either side of the relationship and will enable you to achieve the following

b = Brand.find(1)
b.categories.all

Update The above is totally untested code and I have just corrected a glaringly stupid mistake I made. If you have any issues implementing this then come back