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

activerecord, count, ruby-on-rails

Solution

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

Problem

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)

Original source