Find Products Matching All Categories (Rails 3.1)

Find Products matching ALL Categories (Rails 3.1)

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}")

How to find items with *all* matching categories

ActiveRecord

For ActiveRecord, you could put a method like this in your Item class:

def self.with_all_categories(category_ids)
  select(:id).distinct.
    joins(:categories).
    where('categories.id' => category_ids).
    group(:id).
    having('count(categories.id) = ?', category_ids.length)
end

Then you can filter your queries like so:

category_ids = [1,2,3]
Item.where(id: Item.with_all_categories(category_ids))

You could also make use of scopes to make it a little more friendly:

class Item
  scope :with_all_categories, ->(category_ids) { where(id: Item.ids_with_all_categories(category_ids)) }

  def self.ids_with_all_categories(category_ids)
    select(:id).distinct.
      joins(:categories).
      where('categories.id' => category_ids).
      group(:id).
      having('count(categories.id) = ?', category_ids.length)
  end
end

Item.with_all_categories([1,2,3])

Both will produce this SQL

SELECT "items".*
FROM "items"
WHERE "items"."id" IN
  (SELECT DISTINCT "items"."id"
   FROM "items"
   INNER JOIN "categories_items" ON "categories_items"."item_id" = "items"."id"
   INNER JOIN "categories" ON "categories"."id" = "categories_items"."category_id"
   WHERE "categories"."id" IN (1, 2, 3)
   GROUP BY "items"."id" 
   HAVING count(categories.id) = 3)

You don't technically need the distinct part of that subquery, but I'm not sure whether with or without would be better for performance.

SQL

There's a couple approaches in raw SQL

SELECT *
FROM items
WHERE items.id IN (
  SELECT item_id
  FROM categories_items
  WHERE category_id IN (1,2,3)
  GROUP BY item_id
  HAVING COUNT(category_id) = 3
)

That will work in SQL Server - the syntax might be slightly different in Postgres. Or

SELECT *
FROM items
WHERE items.id IN (SELECT item_id FROM categories_items WHERE category_id = 1)
  AND items.id IN (SELECT item_id FROM categories_items WHERE category_id = 2)
  AND items.id IN (SELECT item_id FROM categories_items WHERE category_id = 3)

Dynamic categories criteria with matching products rails 3

In my opinion, it's better not to define additional tables to handle this due lots of performance issues. My preference to handle such things is to use a serialized column in the products table. Ability to search directly in the database is reduced with this approach, but then you wouldn't want to do that anyway. To handle search, you have to add some sort of indexed searching mechanism. Like acts_as_ferret or even Solr or ElasticSearch.

If you are using postgres check out https://github.com/softa/activerecord-postgres-hstore

For Mysql, use the rails's built in 'store'
http://api.rubyonrails.org/classes/ActiveRecord/Store.html

class Product < ActiveRecord::Base
  has_and_belongs_to_many :categories
  store :settings
end

To set criteria for each category do something similar to this:

class Category < ActiveRecord::Base
  has_and_belongs_to_many :products

  def criteria
     @criteria_list ||= self[:criteria].split('|')
     @criteria_list
  end

  def criteria=(names)
    self[:criteria] = names.join('|')
  end

end

Everytime a product is added to a category, check if all of the criteria in that category is available in the product's properties hash keys. If not, add it with a default value if needed.

You can also setup accessors for the properties hash store using a proc that dynamically gets the accessor names from the all the criteria field of the categories of the product? (not sure about this, cause I haven't done this before)

You can also look into using STI (Single table Inheritance) using a type field in your products table. (It's well documented) This approach is slightly better 'cause when products move from one category to another, the properties won't change.

class Gadget < Product
   store_accessor :manufacturer, :model
end

class Phone < Gadget
   store_accessor :os, :touch_screen, :is_smart
end

Hope this helps

How can I filter products by multiple categories with ActiveRecord?

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')

Filtering by categories with rails

You can achieve this with group and having:

scope :filter, -> (category_ids) { 
  return unless category_ids.present?
  includes(:categories).where(categories: { id: category_ids })
                       .group(:id).having("count(*) = ? ", category_ids.size)
}

ActiveRecord find categories which contain at least one item

please notice, what the other guys answererd is NOT performant!

the most performant solution:

better to work with a counter_cache and save the items_count in the model!

scope :with_items, where("items_count > 0")

has_and_belongs_to_many :categories, :after_add=>:update_count, :after_remove=>:update_count

def update_count(category)
  category.items_count = category.items.count
  category.save
end

for normal "belongs_to" relation you just write

belongs_to :parent, :counter_cache=>true

and in the parent_model you have an field items_count (items is the pluralized has_many class name)

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

in a has_and_belongs_to_many relation you have to write it as your own as above

---

Related Topics