How to add a COUNT column to a working query with group() in Rails 3.1

Question

I have a list of UserProfile which may (or may not) have one or more widgets:

class UserProfile < ActiveRecord::Base
  has_many :widgets
end

@all_profiles = UserProfile.joins(:widgets).group('user_profiles.id').order("name ASC")

In the index view, instead of doing a new query in EACH line of the view to get the count of widgets for @all_profile.each, is there a way to add a count column to the original that shows how many widgets were in each grouped row? (Eg, avoid re-querying the database N times if there are N @all_profiles ?

(this is important not only to avoid N extra queries, but it allows us to add a SORT option on the count column so we can sort by customers with the most widgets)

Answer 1

You can add a COUNT column via ActiveRecord::QueryMethods#select

@all_profiles = UserProfile.joins(:widgets).select('user_profiles.*, COUNT(*) AS widget_count').group('user_profiles.id').order('name ASC')

Now, each of the returned objects will contain a widget_count attribute that you can access just like any other model attribute:

@all_profiles.each do |profile|
  puts profile.widget_count
end