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
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
category.save
end
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)
http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html
in relation to has_and_belongs_to_many you should write it as your own as above
source to share
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:
Category.joins(:items).uniq
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
source to share
Category.joins(:items)
More details here: http://guides.rubyonrails.org/active_record_querying.html#joining-tables
source to share
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"
.
source to share