views:

74

answers:

2

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" ?

A: 
Score.find(:select => "player_name,max(current_score) as high_score",:group_by => "player_name",:order => "max(current_score) DESC")
davydotcom
I'm trying to work out the ranking of a particular player. Your query will just sort them if I'm correct. I added some further clarification to the question.
Leddo
+1  A: 

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
Shadwell
That's perfect! I just made a change to +1 to the ranking as if there are 0 score greater than my ranking would be 1 instead of 0. You Rock!
Leddo