CODEWITHSUNDEEP
mysqlruby-on-rails
darkginger
darkginger

Reputation: 690

Using .order to return a value based on where a user ranks in a table in Rails 4

I am trying to return the ranking of a user in a table, and I am stumped.

First off, I have a table that captures scores in my game called Participation. For two players, it would contain results with a user_id, game_id, and finally a score, like so:

<Participation id: 168, user_id: 1, game_id: 7, ranking: 0, created_at: "2016-04-07 05:36:48", updated_at: "2016-04-07 05:36:58", finished: true, current_question_index: 3, score: 2>

And then a second result may be:

<Participation id: 169, user_id: 2, game_id: 7, ranking: 0, created_at: "2016-04-07 05:36:48", updated_at: "2016-04-07 05:36:58", finished: true, current_question_index: 3, score: 1>

If I wanted to show a leaderboard of where users placed, it would be easy, like: Participation.where(game_id: "7").order(:asc). I am doing that now successfully.

Instead though, I want to return a result of where a user ranks in the table, if organized by score, against the competition. For bare bones, in the example above, I would have user 1 and user 2, both played game 7, and:

How can I rewrite that participation statement in my controller to check where a user ranks for a matching game_id based on score and then assign an integer value based on that ranking?

For bonus points, if I can have the controller return that value (like 1 for user_id 1), do you think it would be a bad idea to use update_attributes to add that to the ranking column rather than breaking out a new table to store user rankings?

Upvotes: 0

Views: 267

Answers (3)

darkginger
darkginger

Reputation: 690

I ended up figuring out a strategy on how to do this. Again, the key thing here is that I want to assign a "rank" to a user, which is effectively an integer representing which "row" they would be in if we were to order all results by the column score.

Here's my process: 

    a = Participation.where(user_id: current_user.id).last.score
    b = Participation.where(user_id: current_user.id).last.id
    scores = Participation.where(game_id: params[:game_id]).where("score > ?", a).count

    if scores == 0 
        Participation.update(b, :ranking => 1)
    else 
        Participation.update(b, :ranking => scores + 1)
    end 

  end

In short, I took a count of how many higher scores there are for a particular result. So if I am user 2 and I have the second highest score, this would count 1 result. Using the if/else logic, I think translate this to the ranking and update my table accordingly.

You could push back here and say the ranking likely would need frequent updates (like a background job), and that is surely true. This method does work to answer my initial question though.

Upvotes: 0

Lymuel
Lymuel

Reputation: 574

Try

Participation.all.order("game_id asc, score desc")

Upvotes: 0

Anthony E
Anthony E

Reputation: 11235

If you're using mysql, try using the ROW_NUMBER function on an ordered query to calculate rank:

Participation.select("user_id, ROW_NUMBER() AS rank").where(game_id: game_id).order(score: :asc)

The generated SQL would be:

SELECT user_id, ROW_NUMBER() AS rank FROM "participations" ORDER BY "participations"."score" ASC

I usually use Postgres so not able to test the query directly, however, it should work.

I'd recommend caching the rank column if you need to access it frequently, or if you need to access rank for a single user/game pair. You'd also need to set up a background job to do this on a recurring basis, perhaps once every 15 minutes or so. However, the benefits of the dynamic query above is that it's more likely to be up to date, but takes time to generate depending on how many participation entries exist for that particular game.

Upvotes: 0

Related Questions