Order products by association count

Question

Class Product

has_many :sales

end

Class Sale

belongs_to :product

end

How do i get the most sold products.. (Product find all.. order by .. ventas..) ?

Answer 1

You might want to hit two birds with one stone (the other bird being performance) by using a counter cache column in the product model. For instance, say you make a sale for product X. When that sale is committed to the database, it will run a callback to increase the number of sales for that product in the product's row in the database. When you destroy, it conversely decreases the number of products.

You'd need to set up a cache column in the products table. In a new migration, do this:

add_column :products, :sales_count, :integer, default => 0

Product.reset_column_information

Product.all.each do |product|
  Product.update_counters(product.id, :sales_count => product.sales.length
end

You'd also need to make some changes to your Product and Sale models, like this:

class Product < ActiveRecord::Base
  has_many :sales
end

class Sale < ActiveRecord::Base
  belongs_to :product, :counter_cache => true
end

Then, instead of having to load all of the sales associations (which, in a large app, would be treacherous), you'd just load the product, and you'd have the number of associated sales in the row itself, for a fraction of the cost of performance.

Hope this helps!

Answer 2

If you are trying to order the products by the number of sales associated with them, it can actually be kind of a difficult problem, depending on your database. Googling dug up an answer from transientink.com, but this solution does not work for me personally. I suspect it's because of differences in how PostgreSQL handles GROUP BY clauses compared to MySQL.

Another option is to enable counter_cache on the product association in Sales (see the link for the details) and sort by the counter cache field.

Another option is to use Ruby for the sorting:

@top_products = Product.find(:all, :include => :sales).sort_by { |p| p.sales.size }

There are 2 major issues with this, the first is that you are leveraging your web server to do the sorting, which will slow down requests (perhaps noticeably, depending on the number of products.) Second, you can't make use of the :limit option in the finder, so if you only want the top 10 most sold products, you have to fetch them all from the DB, sort them in your application, and then limit them afterwards.