Ranking in Django ORM or SQL?

Question

I have a huge list rated by various values (e.g. scores)

So, I take a list ordered by these values:

players = Player.objects.order_by('-score', '-karma')

---

I would like to:

• Take a player and get nearby players

Rating P1: 123

Rating P2: 122

you ! rating: 110

Rating P3: 90

Rating P2: 89

• Take a position !

You rated # 1234 for rating

You are in the ranking # 9876 for karma.

---

Help would be greatly appreciated. thank:)

Answer 1

To get a user rating:

(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 

id_of_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;

player_ranking - , .

, !

: MySQL rank() (MS SQL, Oracle, Postgres ). , , , MySQL: http://www.artfulsoftware.com/infotree/queries.php?&bw=1024#460.

Answer 2

. .

, , , . ( , , , .)

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 3

I did this with 3 queries with ORM, but I think fewer queries would be better:

user_rank = Score.objects.filter(high_score__gt=user_score.high_score).count() + 1

neighbour_scores = Score.objects.filter(game_id=gme,~Q(user_id = usr),high_score__gte=user_score.high_score).order_by('high_score')[:offset]
neighbour_scores.append(user_score)
neighbour_scores.append(Score.objects.filter(game_id=gme, high_score__lt=user_score.high_score).order_by('-high_score')[:offset])