CODEWITHSUNDEEP
ruby-on-railsactiverecordrails-activerecord
Kenn
Kenn

Reputation: 189

How can I filter products by multiple categories with ActiveRecord?

I'd like to add the ability to filter products by multiple categories to a Rails ecommerce application. I currently allow filtering products by category, but now I'd like to provide the ability to filter further.

For example, I'd like to allow a user to select "Men's" and "Outerwear" to display only products in both of those categories.

Knowing that supplying an array of category IDs in my Product query will find products in any of the specified categories, and hoping for a nice ActiveRecord-y type solution, I first tried adding multiple categories.id conditions in the query, but this didn't work out.

Product.joins(:categories).where(:'categories.id' => 123, :'categories.id' => 456)

The result here was that the first category ID was overwritten by the second.

And, of course, this will find products in either of the categories, rather than only products in both:

Product.joins(:categories).where(:'categories.id' => [123, 456])

Additionally, the solution I need should work with an arbitrary number of categories. It could be two, it could be three, or more.

After doing some research, I don't think there's a nice Rails-y way to do this, but I'm stuck on finding the actual correct solution.

My application is running Rails 5.2 with MariaDB.

Upvotes: 2

Views: 3441

Answers (1)

Kenn
Kenn

Reputation: 189

Based on @muistooshort's comment above, I found this SO post with the solution I needed: Selecting posts with multiple tags

Updating my query like so gave the products I wanted, those in ALL of the specified categories (lines wrapped for readability):

Product.joins(:categories).where(:categories => { :id => category_ids })
    .having('count(categories.name) = ?', category_ids.size)
    .group('products.id')

Upvotes: 2

Related Questions