Rails order by associated object number

Question

Hey,

The title of this question might be a bit off but its the closest I could get to what I am trying to do.

I have a Products model which has_many Comments. I am looking to come up with a way to grab the top 10 Products with the most comments. Is this possible?

At the moment I have:

Product.find(:all, :limit => 10)

This gets me my 10 products, but it obviously does not consider how many comments each product has.

Cheers

Eef

Answer 1

If you have a counter cache column on Product for Comment then you can do this with a simple :order_by on that column.

Edit: Taking animal's comment into account:

class Product < ActiveRecord::Base
  has_many :comments,
    :counter_cache => true
end

You will need a column on products called comments_count:

add_column :products, :comments_count, :integer, :null => false, :default => 0

Answer 2

Alternatively if you don't want to alter your model you can use find_by_sql to specify a subselect which will tell you how many comments each product has and order by that. E.g.

Post.find_by_sql("SELECT products.*
  FROM products, (SELECT COUNT(*) 
    FROM comments WHERE comments.product_id = products.id) AS product_comment_count
  ORDER BY product_comment_count DESC
  LIMIT 10")

You might need to adjust the syntax to be specific to whichever RDBMS you are using.

Answer 3

Rails probably has a function for this that I don't know about. This should work, but is untested.

Product.find(:all, :limit => 10).sort do |a,b|
    a.comments.size <=> b.comments.size
end

Depending on the order, you may want to swap a and b.

Answer 4

Try this:

Product.all(:select=> "products.*, COUNT(products.id) AS comments_count",
            :joins => :comments, 
            :group => "products.id", 
            :order => "comments_count",
            :limit => 10)

Result is sorted by the comment count and you can access the comments_count as follows:

@products.each do |product|
  p product.comments_count
end

Make sure you have indexed the product_id column in the comments table.

Note:

I would use the counter_cache feature for this requirement(as suggested by @tadman).