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: