How to sort Rails AR.find by number of objects in a has_many relationship

Question

How can I write an AR find query to have the results ordered by the number of records in a has_many association?

class User < ActiveRecord::Base
  has_many :photos
end

I want to do something like...

User.find(:all, :order => photos.count)

I realize my find is not valid code. Say I have the following data.

User 1, which has 3 photos 
User 2, which has 5 photos 
User 3, which has 2 photos 

I want my find to bring me back the users in the order of...

User 2, 
User 1, 
User 3 

based on the count of of the users photos

Answer 1

The easiest way to achieve this is probably to add a counter cache to that model and then sort by that column.

class Photo < ActiveRecord::Base
  belongs_to :user, :counter_cache => true
end

And be sure to add a column to your users table called photos_count.

Then you will be able to...

User.find(:all, :order => 'photos_count')

Answer 2

If you don't want an extra column, you could always ask for an extra column in the returned result set:

User.all(:select => "#{User.table_name}.*, COUNT(#{Photo.table_name}.id) number_of_photos",
         :joins => :photos,
         :order => "number_of_photos")

This generates the following SQL:

SELECT users.*, COUNT(photos.id) number_of_photos
FROM `users` INNER JOIN `photos` ON photos.user_id = users.id
ORDER BY number_of_photos

Answer 3

If you don't want to add a counter cache column, your only option is to sort after the find. If you :include the association in your find, you won't incur any additional database work.

users = User.find(:all, :include => :photos).sort_by { |u| -u.photos.size }

Note the negative sign in the sort_by block to sort from high to low.

Answer 4

I would advise you not to write direct SQL, since implementations of it may vary from store to store. Fortunately, you have arel:

User.joins(:photos).group(Photo.arel_table[:user_id]).
     order(Photo.arel_table[:user_id].count)