I have a huge list ranked by various values (eg. scores)
So I grab the list ordered by these values:
players = Player.objects.order_by('-score', '-karma')
I would like to:
P1 score:123
P2 score:122
YOU! score:110
P3 score:90
P2 score:89
You are ranked #1234 for score
You are ranked #9876 for karma
Help would be very much appreciated. thanks :)
These sorts of things are always very difficult to do. You'll need multiple queries for each one.
So, to get the players just before and after your position when ordered by score, first you'll need to work out what that position is. (Note that this assumes that you can't have more than one person with the same score, which may not necessarily be true.)
me = Player.objects.get(pk=my_pk)
position = Players.objects.all().filter(
score__lte=me.score).order_by('-score').count()
players = Players.objects.all()[position-2:position+2]
To get the ranking of the user:
(SELECT * FROM (
SELECT
RANK() OVER (ORDER BY Score desc ,Karma desc) AS ranking,
Id,
Username,
Score, karma
FROM Players
) AS players_ranked_by_score
where Id = id_of_user
Where id_of_user is the parameter containing the id of the current player. To get the neighboring players and the current user:
(SELECT * FROM (
SELECT
RANK() OVER (ORDER BY Score desc ,Karma desc) AS ranking,
Id,
Username,
Score, karma
FROM Players
) AS all_players_ranked
where ranking >= player_ranking - 2 and ranking <= player_ranking + 2;
Where player_ranking is the ranking obtained from the query above.
Hope it helps!
Update: MySQL does not have a rank() function (MS SQL, Oracle, Postgres have one). I looked around and I got this link explaining how to do ranking in MySQL: http://www.artfulsoftware.com/infotree/queries.php?&bw=1024#460.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With