Getting a Rank from Activerecord

Getting a rank from ActiveRecord

You can count the number of users having less points, and add one.

def rank
  User.where("points > ?", points).count + 1
end

This would return 3 for users 1 and 3.

Know the position of a query with ActiveRecord (RoR) after filtering for a ranking table

Here's a hint: count the number of users who have a best score greater than the current user's best score. Add 1 to that count, and that gets you the current user's rank.

For the sake of efficiency, consider storing each user's best score with the user record and updating it whenever a user finishes a game session. Add an index to the best_score column on the users table so that you can do the count quickly.

To generate the leader board, just sort users by their best score and limit to the top 20.

Some code:

 class User
   def rank
     User.where("best_score > ?", best_score).count + 1
   end

   def self.leaders
     order("best_score desc, id asc").limit(20)
   end
 end

Then you can write:

 current_user.rank  # returns ranking of user as an integer
 User.leaders       # returns a list of 20 top scorers, highest scorer first

To know whether the current user is in the top 20, you can just check

if current_user.rank <= 20

I see above that there was some discussion about how to handle cases where two or more users have the same score. The code above generates identical ranking numbers for same-score users, so if the 2nd and 3rd users have the same score, your rankings may look like 1, 2, 2, 4, 5, 6. I think this is a common way to handle it.

To help ensure stable ordering, I added order id asc so that when two or more users have the same score, the older user (the one with the lower user id) will be listed first.

One final bit: when you do the migration to add best_score to your users table, set default: 0 and allow_nil: false so that you don't get into trouble with users who have played no games. Either that, or adjust the rank function so that it does something reasonable (like return nil or MAXINT) for users who haven't played any games and thus have no best_score.

Ranked Model getting started

Per our conversation via comments. The problem looks like you need to seed your database before you can use the rank command.

So you can seed your dev database a few ways, via rails console, using your app or modifying your seeds.rb file.

I'm going to suggest you use your seeds.rb file because it's idempotent ( http://en.wikipedia.org/wiki/Idempotence )

So open db/seeds.rb or create one if you don't have one.

Create a few records:

Costproject.create(rank_order: 3, coststatus_id: 2, project_year: 2016)
Costproject.create(rank_order: 2, coststatus_id: 2, project_year: 2016)
Costproject.create(rank_order: 1, coststatus_id: 2, project_year: 2016)

This will insert 3 records into costprojects table. You MUST have the coststatus_id and project_year set to those values per your scope. These won't work:

Costproject.create(rank_order: 10)
Costproject.create(rank_order: 20, coststatus_id: 10)
Costproject.create(rank_order: 30, coststatus_id: 2, project_year: 2013)

then via terminal you can run:

bundle exec rake db:seed

This rake task will run your seed file as if you were manually typing it in your rails console.

Now you should be able to do Costproject.rank(:row_order).all. The first model should have an id of 3, assuming you ran your seed file on an empty database.

For more info:

What is the best way to seed a database in Rails?

http://railscasts.com/episodes/179-seed-data

http://www.jasonwieringa.com/rake_rails_and_databases/

---

Related Topics