CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
ZelluX
ZelluX

Reputation: 72585

ActiveRecord find categories which contain at least one item

Support I have two models for items and categories, in a many-to-many relation

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 which contain at least one items, what will be the best way to do this?

Upvotes: 1

Views: 1186

Answers (4)

Stu
Stu

Reputation: 154

I would like to echo @Delba's answer and expand on it because it's correct - what @huan son is suggesting 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 it's a many-to-many you only want DISTINCT categories to come back:

Category.joins(:items).uniq

Using the joins query will let you more easily work conditions into your count of items too, giving much more flexibility. For example you might not want to count items where enabled = false:

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

This would generate the following SQL, using inner 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

Upvotes: 2

Tim Kretschmer
Tim Kretschmer

Reputation: 2280

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

Upvotes: 0

Sean Hill
Sean Hill

Reputation: 15056

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

This will return all categories which have an entry in the join table because, ostensibly, a category shouldn't have an entry there if it does not have an item. You could add a AND categories_items.item_id IS NOT NULL to the subselect condition just to be sure.

In case you're not aware, table_name is a method which returns the table name of ActiveRecord class calling it. In this case it would be "categories".

Upvotes: 0

Damien
Damien

Reputation: 27463

Category.joins(:items)

More details here: http://guides.rubyonrails.org/active_record_querying.html#joining-tables

Upvotes: 1

Related Questions