ActiveRecord find categories containing at least one element

Support. I have two models for items and categories in a many-to-many relationship.

class Item < ActiveRecord::Base
  has_and_belongs_to_many :categories 

class Category < ActiveRecord::Base
  has_and_belongs_to_many :items


Now I want to filter out categories that contain at least one item, what is the best way to do this?


source to share

4 answers

note that the other guys do NOT answer.!

the most efficient solution:

better work with counter_cache and store 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


for a normal "belongs to" relationship, you just write

belongs_to :parent, :counter_cache=>true


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

in relation to has_and_belongs_to_many you should write it as your own as above



I'd like to echo @Delba's answer and expand on it because it's correct - what @huan's son suggests with the count column is completely unnecessary if you have your indexes set up correctly.

I would add that you probably want to use .uniq as many of the many you want DISTINCT categories to return:



Using a union query will allow you to more easily fulfill conditions on your cardinality, providing much more flexibility. For example, you might not want to count items where enabled = false:

Category.joins(:items).where(:items => { :enabled => true  }).uniq


This will generate the following SQL using internal joins, which are EXTREMELY fast:

SELECT `categories`.* FROM `categories` INNER JOIN `categories_items` ON `categories_items`.`category_id` = `categories`.`id` INNER JOIN `items` ON `items`.`id` = `categories_items`.`item_id` WHERE `items`.`enabled` = 1


Good luck, Stu





More details here:



scope :has_item, where("#{table_name}.id IN (SELECT categories_items.category_id FROM categories_items")


This will return all categories that have an entry in the join table, because supposedly a category shouldn't have an entry there if it doesn't have an item. You can add a condition AND categories_items.item_id IS NOT NULL

to the subquery condition just to be sure.

If you don't know, table_name

this is a method that returns the table name of the ActiveRecord class that is calling it. In this case it will be "categories"




All Articles