Ranking within Django ORM or SQL?

Question

Hi, so...

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 :)

Answer 1

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]

Answer 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.