Ranking within Django ORM or SQL?

django, django-models, django-orm, python, sql

Solution

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.

Problem

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: - Grab a player and get the neighbouring players P1 score:123 P2 score:122 YOU! score:110 P3 score:90 P2 score:89 - Grab the position! You are ranked #1234 for score You are ranked #9876 for karma Help would be very much appreciated. thanks :)

Original source