redemption in a blog

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

December 16, 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. 'www.rubyonrails.org'
end

# 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], ['www.site, 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'
end

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:

Posted by Chu Yeow at December 16, 2007 SGT | Permalink | | Category: Open Source, Ruby on Rails