Help optimizing ActiveRecord query (voting system)

Question

I have a voting system with two models: Item(id, name) and Vote(id, item_id, user_id).

Here's the code I have so far:

class Item < ActiveRecord::Base
  has_many :votes

  def self.most_popular
    items = Item.all #where can I optimize here?
    items.sort {|x,y| x.votes.length <=> y.votes.length}.first #so I don't need to do anything here?
  end
end

There's a few things wrong with this, mainly that I retrieve all the Item records, THEN use Ruby to compute popularity. I am almost certain there is a simple solution to this, but I can't quite put my finger on it.

I'd much rather gather records and run the calculations in the initial query. This way, I can add a simple :limit => 1 (or LIMIT 1) to the query.

Any help would be great--either rewrite in all ActiveRecord or even in raw SQl. The latter would actually give me a much clearer picture of the nature of the query I want to execute.

Answer 1

Probably there's a better way to do this in ruby, but in SQL (mysql at least) you could try something like this to get a top 10 ranking:

SELECT i.id, i.name, COUNT( v.id ) AS total_votes
FROM Item i
LEFT JOIN Vote v ON ( i.id = v.item_id ) 
GROUP BY i.id
ORDER BY total_votes DESC 
LIMIT 10

Answer 2

Group the votes by item id, order them by count and then take the item of the first one. In rails 3 the code for this is:

Vote.group(:item_id).order("count(*) DESC").first.item

In rails 2, this should work:

Vote.all(:order => "count(*) DESC", :group => :item_id).first.item

Answer 3

One easy way of handling this is to add a vote count field to the Item, and update that each time there is a vote. Rails used to do that automatically for you, but not sure if it's still the case in 2.x and 3.0. It's easy enough for you to do it in any case using an Observer pattern or else just by putting in a "after_save" in the Vote model.

Then your query is very easy, by simply adding a "VOTE_COUNT DESC" order to your query.

Answer 4

sepp2k has the right idea. In case you're not using Rails 3, the equivalent is:

Vote.first(:group => :item_id, :order => "count(*) DESC", :include => :item).item