Select all Users, order them by their comments count

In a Rails application I want to fetch all users, ordered by their count of comments, to get the top-ten commenters. How do I do that, specifically?

In my model, User and Comment are in a HABTM relationship to each other.

Right now I have:

  all_users = User.all :joins => :comments
  all_users.sort_by do |user| 
    user.comments.count
  end

I'm still stuck with the fact that this delivers multiple entries for one user, as one user might have more than one comment. And I still have to get the first ten users o开发者_开发问答nly.

What would be a more efficient way to do this, e.g. in the query itself?

Thanks

If you had your belongs_to :user in the Comment model configured like this:

belongs_to :user, :counter_cache => true

And then you had a comments_count integer field defaulting to 0 in your users table, then you would be able to order by that rather than having to do a join.

you can always do

User.all(:joins => :comments, :group => 'users.id', :order => 'count(*)')

if it does not hit your db performance