Find users with at least 'n' items
Published over 6 years ago
This question is asked quite a few times in #rubyonrails
When your models look like :
class User < ActiveRecord::Base has_many :items end class Item < ActiveRecord::Base belongs_to :user end
How do you find all the users with at least ‘n’ number of items ?
Here’s how :
User.find :all, :joins => "INNER JOIN items ON items.user_id = users.id", :select => "users.*, count(items.id) items_count", :group => "items.user_id HAVING items_count > 5"
This will give you all the users with at least 5 items.
The statement is using INNER JOIN to eliminate users with no items. Also, in :select, there is count(items.id) aliased items_count and in :group is items.user_id_. This will group items by userid and also count number of items per user. Now, database requires HAVING clause when you want to supply conditions for group functions ( items_count in our case ). ActiveRecord, as of now, doesn’t provide :having key for find(). Hence, we need to use a very little hack ( more like workaround ) to overcome that and supply HAVING clause in :group key.
May be someone interested can submit a patch for :having key in AR finders.