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.
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”
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! :-)
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.
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 :
In short, you can use the same logic for all types of group calculations : sum, avg, total, etc.
And oh, btw, sql is no trickery! It’s fun and it’s good :-)
Thanks for providing this example. It helped us solve a tricky problem at CollabRx today.
Thanx for the tip ! i was looking for that
I was looking for it too. Thanks!