CODEWITHSUNDEEP
ruby-on-railssumranking
Twiddr
Twiddr

Reputation: 297

Ranking position

I have a Rails application with the following models:

User has many_bets and Bets belongs_to User. Every Bet has a Profitloss value, which states how much the User has won/lost on that Bet.

So to calculate how much a specific User has won overall I cycle through his bets in the following way:

 User.bets.sum(:profitloss)

I would like to show the User his ranking compared to all the other Users, which could look something like this:

"Your overall ranking: 37th place"

To do so I need to sum up the overall winnings per User, and find out in which position the current user is.

How do I do that and how to do it, so it don't overload the server :)

Thanks!

Upvotes: 3

Views: 2531

Answers (3)

iouri
iouri

Reputation: 2929

Best way to do it is to keep a pre calculated total in your database either on user model itself or on a separate model that has 1:1 relation to user. If you don't do this, you will have to calculate sum for all users at all times in order to get their rating, which means full table operation on bets table. This said, this query will give you desired results, if more than 1 person has the same total, it will count both as rating X:

select id, (select count(h.id) from users u inner join 
(select user_id, sum(profitloss) as `total` from bets group by user_id) b2
on b2.user_id = u.id, (select id from users) h inner join
(select user_id, sum(profitloss) as `total` from bets group by user_id) b 
on b.user_id = h.id where u.id = 1 and (b.total > b2.total)) 
as `rating` from users where id = 1;

You will need to plug user.id into query in where id = X

if you add a column to user table to keep track of their total, query is a little simpler, in this example column name is total_profit_loss:

select id, total_profit_loss, (select count(h.username)+1 from users u, 
(select username, score from users) h 
where id = 1 and (h.total_profit_loss > u.total_profit_loss)) 
as `rating` from users where id = 1;

Upvotes: 1

Baldrick
Baldrick

Reputation: 24340

If you have a large number of Users and Bets, you won't be able to compute and sort the global profitloss of each user "on demand", so I suggest that you use a rake task that you schedule regularly (once a day, every hour, etc...)

Add a column position in the User model, get the list of all Users, compute their global profitloss, sort the list of Users with their profitloss, and finally update the position attribute of each User with their position in the list.

Upvotes: 1

rewritten
rewritten

Reputation: 16435

You can try something similar to

User.join(:bets).
    select("users.id, sum(bets.profitloss) as accumulated").
    group("users.id").
    order("accumulated DESC")

and then search in the resulting list of "users" (not real users, they have only two meaningful attributes, their ID and a accumulated attribute with the sum), for the one corresponding to the current one.

In any case to get a single user's position, you have to calculate all users' accumulated, but at least this is only one query. Even better, you can store in the user model the accumulated value, and query just it for ranking.

Upvotes: 4

Related Questions