Find users with at least 'n' items 10

Posted by pratik
on Thursday, November 01

This question is asked quite a few times in #rubyonrails

When your models look like :

1
2
3
4
5
6
7

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 user_id 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.

Comments

Leave a response

  1. Jack Danger – November 01, 2007 @ 01:23 PM

    Good point Pratik – hopefully somebody puts together a patch.

    Note: You could also use the new :joins feature to save typing: User.find :all, :joins => :items, :select => “users.*, count(items.id) items_count”, :group => “items.user_id HAVING items_count > 5”

  2. Pratik – November 01, 2007 @ 01:37 PM

    Hey Jack,

    IIRC, :select doesn’t work with new :joins because it uses same codebase as eager loading. That’d one reason I’ve grown not to like the joins implementation, because it fires the same fat assed query as eager loading. Maybe I’ll refactor it when I get some time.

    Thanks for all the comments you’ve left on my blog! :-)

  3. Eric Anderson – November 01, 2007 @ 02:52 PM

    Just use a counter cache. Then you know how many items a user has without all this trickery. Once the counter cache is setup just:

    User.find :all, :conditions => [‘items_count > ?’, 5]

    Nice and simple. Exactly the type of thing the counter cache is for.

  4. Pratik – November 01, 2007 @ 03:03 PM

    Eric, this example is not only for count, but can also be changed to be used in many other situations. For example, if the items table has a column called cost and you want to find out all the users who have spent more than 5000$ in total, you can just do something like :

    

    User.find :all, :joins => "INNER JOIN items ON items.user_id = users.id", :select => "users.*, sum(items.cost) items_value", :group => "items.user_id HAVING items_value > 5000"

    In short, you can use the same logic for all types of group calculations : sum, avg, total, etc.

  5. Pratik – November 01, 2007 @ 03:13 PM

    And oh, btw, sql is no trickery! It’s fun and it’s good :-)

  6. Tony Perrie – October 23, 2008 @ 10:49 PM

    Thanks for providing this example. It helped us solve a tricky problem at CollabRx today.

  7. Bensoussan Michael – November 19, 2008 @ 01:50 PM

    Thanx for the tip ! i was looking for that

  8. Taiya – December 20, 2008 @ 01:48 AM

    I was looking for it too. Thanks!

  9. Jayanth – March 17, 2009 @ 10:31 AM

    Hi sir,
    Im working on ruby on rails 2.0 for last 3months..
    I am feeling little perplexed with Action Mailer (automatic mails sending)
    please give me some links which can help me out
    or else guide me how to succeed in this action mailer
    Thanks in advance

  10. Christopher G – August 05, 2009 @ 08:24 PM

    Gorgeous post, works great, saved me who knows how much time.

    Is this idiom updated with later rails versions now?

Comment