Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Ranking within Django ORM or SQL?

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

like image 921
RadiantHex Avatar asked Jan 26 '10 19:01

RadiantHex


2 Answers

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]
like image 76
Daniel Roseman Avatar answered Sep 27 '22 17:09

Daniel Roseman


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.

like image 22
Juan Tarquino Avatar answered Sep 27 '22 18:09

Juan Tarquino