An interesting undocumented piece of ActiveRecord I bumped into today is “count_all”. You can use this inside ActiveRecord count calls to do sorts and limits based on the count when you’re doing GROUP BY.
I came across this because I was having a bit of trouble recently finding the Rails way to find the biggest student group, and when the most recent joining of that group happened. What I wanted was a Railsy way to run this query:
SELECT member_of_id, count(*) mems, max(created_at) most_recent FROM memberships WHERE member_of_type = 'Organization' GROUP BY member_of_id ORDER BY mems DESC LIMIT 1
What this SQL does is return the largest group, the number of members it has, and the time the most recent one was created. It’s pretty fast, too. Now to make it Rails!
ActiveRecord provides us with the powerful count method, which will get us a good portion of the way there, using the group keyword:
Membership.count(:group => "member_of_id", :conditions => "member_of_type = 'Organization'")
This is more Railsy, but it returns a lot of data, and we will need to sort it in memory, and discard the bulk of what we pulled from the database, so it’s a good deal slower. What’s we’d like to do is sort it in the database, and only return the top row. It turns out you can do this:
largest_organization = Membership.count(:group => "member_of_id", :conditions => "member_of_type = 'Organization'", :order => "count_all DESC", :limit => 1)
largest_organization_id = largest_organization.first[0] unless largest_organization.blank?
This may not be perfectly Rails Way, since I’m not sure how legit it is to use count_all in the order by clause. It’s also not quite as fast as the original SQL, because we still need to run another query to get the most recent join time for the organization:
Membership.maximum(:created_at, :conditions => ["member_of_id = ?", largest_organization_id])
Still, it’s pretty good, and stays inside the standard ActiveRecord paradigm.
Enjoy!