Find users with at least 'n' items
Published almost 7 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.