Sensible GROUP BYs in ActiveRecord::Calculations – Rails 2.0 a feature a day #5

In: Open Source|Ruby on Rails

16 Dec 2007

Doing GROUP BYs in ActiveRecord has always been tricky. There’s a lot of “magic” in ActiveRecord and room for untested edge cases. Thankfully, Rails also has a squadron of eagle-eyed contributors who are on hand to fix unforeseen errors.

For example, if you wanted to do this:

class Click < ActiveRecord::Base
  belongs_to :site  # site_id foreign key is a String, e.g. ''

# Now, try counting the number of clicks, grouped by site.
Click.count(:all, :group => :site)
# => [[nil, 1], [nil, 3]]

This is a surprisingly common thing to do especially when you are producing reports or statistics of any kind and unfortunately decided to go with a non-(auto)numeric foreign key. As you may have noticed, the sites were returned as nil which is totally unhelpful. Thankfully, this has been fixed in Rails 2.0:

Click.count(:all, :group => :site)
# => [[nil, 1], [', 3]]

GROUP BY and :foreign_key don’t mix

This change is NOT in Rails 2.0.

If you wanted to do GROUP BYs in your ActiveRecord models (via the :group option), you had to take note of one particular gotcha. For example, with an ActiveRecord model like this:

class Anime < ActiveRecord::Base
  belongs_to :japanese_studio, :class_name => 'Studio', :foreign_key => 'studio_id'

Note that we specified the :foreign_key option since it can’t be inferred from the association name of ‘japanese_studio’. If we wanted to counting the number of anime grouped by studio though, we’re in big trouble:

Anime.count(:all, :group => :japanese_studio)
# ActiveRecord fails and mumbles about an unknown "japanese_studio_id" key.

Basically what happened is Rails did not use the specified foreign key column of ‘studio_id’, inferring it instead from the association name of ‘japanese_studio’. This is not yet fixed but there’s already a patch ready for it at the Rails issue tracker. In fact, I’m gonna verify it right after posting this.

About the contributor, Kamal Fariz

I thought it was time to cover Rails 2.0 contributions by someone closer to home (“home” being my home, Singapore) so I singled out Kamal Fariz (WorkingWithRails profile), a Rails developer working in Malaysia for a Rails shop. Kamal’s also a former Rails Hackfest winner (read the post-Rails Hackfest interview). Kamal is also an active member of the Malaysia Ruby Brigade (I guess it’s not called a “Ruby User Group” since “RUG” sounds rather unglamorous).

Wow look at the number of parentheses I used in the last paragraph. For no reason whatsoever here’s a lolcats picture:

Parenthese cats are parenthetic

4 Responses to Sensible GROUP BYs in ActiveRecord::Calculations – Rails 2.0 a feature a day #5


ActiveRecord::Base.with_scope { :only => ‘in your model’ } - Rails 2.0 a feature a day #4 - redemption in a blog

December 16th, 2007 at 3pm

[…] « Sensible GROUP BYs in ActiveRecord::Calculations | Main | Concatenate your stylesheets and JavaScripts in 3 seconds – Rails 2.0 a feature a day #3 […]



December 16th, 2007 at 10pm

Those are some fat cats!


Kamal Fariz

December 17th, 2007 at 9am

I almost forgot that I made the patch as it was opened 8 months ago! I was reading and following along until I hit the about the contributor :) Thanks for mentioning it.

I always thought “Ruby Brigade” was the de rigueur term for a bunch of Ruby and Rails enthusiasts, hence the short form Malaysia.rb, singapore.rb, Seattle.rb, etc.

I’ve also noticed that you’ve been on a contribution roll – nice work!


Tecker.LOG » Blog Archive » ActiveRecord::Calculations????GROUP BYs?????Rails 2.0?? #5(??)

June 3rd, 2008 at 11pm

[…] ???Sensible GROUP BYs in ActiveRecord::Calculations – Rails 2.0 a feature a day #5 […]