CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Kevin Southworth
Kevin Southworth

Reputation: 393

Find Products matching ALL Categories (Rails 3.1)

I am struggling with an ActiveRecord query in Rails 3.1.1.

I have 2 models, Product and Category, with a has_and_belongs_to_many from Product to Category (a Product can have many categories).

I am trying to write a search query that will find the Products that have ALL the specified Categories (think of a search UI where the user can choose X categories to search on).

I can do this:

results = Product.joins(:category_products).where('category_products.category_id' => [1,5,8])

but that returns Products that have ANY of those Categories (i.e. produces a SQL "IN(1,5,8)" clause).

How can I create a query that will do an ALL style of matching? E.g. find the products that have ALL of those category_ids...

Upvotes: 0

Views: 1035

Answers (2)

Lukas Eklund
Lukas Eklund

Reputation: 6138

You can do this with a having clause:

@ids = [1,5,8]
query = Product.select('products.id,products.name').joins(:categories) \
               .where(:categories => {:id => @ids}) \
               .group('products.id, products.name') \
               .having("count(category_products.category_id) = #{@ids.length}")

Upvotes: 4

dimuch
dimuch

Reputation: 12818

results = Product.joins(:category_products)
[1,5,8].each do |category|
  results = results.where('category_products.category_id' => category)
end

Upvotes: 0

Related Questions