Как добавить аннотацию глобального рейтинга к последующим запросам Django?

У меня есть модель Django, которая выглядит следующим образом:

class LeaderboardScore(models.Model):
  score = models.FloatField()
  player_account = models.ForeignKey(PlayerAccount, on_delete=models.CASCADE)
  timestamp = models.DateTimeField()

Здесь представлены лучшие результаты в игре. Оценки ранжируются по убыванию баллов и по возрастанию временной метки, так что если два балла одинаковы, то самый ранний балл будет ранжироваться выше.

Каждый раз, когда я делаю запрос к таблице лидеров, я хотел бы включать глобальный рейтинг каждого экземпляра в результат запроса. Я могу успешно сделать это при запросе всех рейтингов следующим образом:

lq = LeaderboardScore.objects.filter(leaderboard__id=1).annotate(
  rank=Window(
    expression=DenseRank(),
    order_by=[F('score').desc(), F('timestamp').asc()]
  )
)

Это правильно отобразит поле с рейтингом. Но если я возьму вышеприведенный запрос и отфильтрую его по player_account следующим образом:

# rank will be 1, but should be the global ranking
lq.filter(player_account__id=123)[0].rank

то рейтинг сбрасывается, и первый элемент в запросе имеет ранг 1, хотя его глобальный ранг должен быть другим. Как сохранить глобальный ранг?

Я не смог понять, как сделать это с помощью ORM в Django, но я смог использовать SQL как временное решение:

WITH ranked_leaderboard AS (
  SELECT
    id,
    score,
    timestamp,
    player_account_id,
    DENSE_RANK() OVER (ORDER BY score DESC, timestamp ASC) AS rank
  FROM
    leaderboards_leaderboardscore
  WHERE
    leaderboard_id = 1
)
SELECT
  id,
  score,
  timestamp,
  rank
FROM
  ranked_leaderboard
WHERE
  player_account_id = 123
ORDER BY
  score DESC, timestamp ASC

Это не идеальное решение, так как оно работает только при поиске конкретного игрока - у меня есть и другие запросы, которые могут состоять из произвольного списка игроков, для которых мне пришлось использовать неоптимальное решение.

Вернуться на верх