Rails 3 - How to get the row number from a model with an order by

Question

Hi, I am putting together a small app that has a leaderboard concept in it. Basically, the model is just a player_name and a current_score.

what I want to do is get the ranking of a specific player, and given that new scores are coming in all the time, it needs to be dynamic. Obviously I could just do a normal find with an order by clause and then I would have to loop through each record. Not very efficient when I could have 100,000's of rows.

Any suggestions on what approach I should be taking?

Here is the migration for the table:

class CreateScores < ActiveRecord::Migration
  def self.up
    create_table :scores do |t|
      t.string  :player_name
      t.integer :current_score

      t.timestamps
    end
  end

  def self.down
    drop_table :scores
  end
end

EDIT As an example, so I have the following:

Score.select('player_name, current_score').limit(20)
=> [#<Score player_name: "Keith Hughes", current_score: 9>, #<Score player_name: "Diane Chapman", current_score: 8>, #<Score player_name: "Helen Dixon", current_score: 4>, #<Score player_name: "Donald Lynch", current_score: 9>, #<Score player_name: "Shawn Snyder", current_score: 2>, #<Score player_name: "Nancy Palmer", current_score: 9>, #<Score player_name: "Janet Arnold", current_score: 1>, #<Score player_name: "Sharon Torres", current_score: 9>, #<Score player_name: "Keith Ortiz", current_score: 5>, #<Score player_name: "Judith Day", current_score: 3>, #<Score player_name: "Gregory Watson", current_score: 7>, #<Score player_name: "Jeremy Welch", current_score: 3>, #<Score player_name: "Sharon Oliver", current_score: 7>, #<Score player_name: "Donald Lewis", current_score: 7>, #<Score player_name: "Timothy Frazier", current_score: 7>, #<Score player_name: "John Richards", current_score: 1>, #<Score player_name: "Carolyn White", current_score: 4>, #<Score player_name: "Ronald Smith", current_score: 2>, #<Score player_name: "Emily Freeman", current_score: 9>, #<Score player_name: "Gregory Wright", current_score: 2>]

How can I work out the ranking of "Donald Lewis" ?

Answer 1

Score.find(:select => "player_name,max(current_score) as high_score",:group_by => "player_name",:order => "max(current_score) DESC")

Answer 2

You could count the number of records that have a higher current_score. You will still have to do it per record (or do a sub-query in your sql).

class Score < ActiveRecord::Base

  def ranking
    Score.count(:conditions => ['current_score > ?', self.current_score])
  end

end