sql - Clean way to use postgresql window functions in django ORM?

Question

I'd like to use postgresql window functions like rank() and dense_rank in some queries in need to do in Django. I have it working in raw SQL but I'm not sure how to do this in the ORM.

Simplified it looks like this:

SELECT
    id,
    user_id,
    score,
    RANK() OVER(ORDER BY score DESC) AS rank
FROM
    game_score
WHERE
    ...

How would you do this in the ORM?

At some point I might need to add partitioning too :|

(we're using Django 1.9 on Python 3 and already depend on django.contrib.postgres features)

Answer 1

Since Django 2.0 it is built-in into the ORM. See window-functions

# models.py
class GameScore(models.Model):
     user_id = models.IntegerField()
     score = models.IntegerField()

# window function usage
from django.db.models.expressions import Window
from django.db.models.functions import Rank

GameScore.objects.annotate(rank=Window(
    expression=Rank(),
    order_by=F('score').desc(),
    partition_by=[F('user_id')]))

# generated sql
SELECT "myapp_gamescore"."id",
   "myapp_gamescore"."user_id",
   "myapp_gamescore"."score",
   RANK() OVER (
     PARTITION BY "myapp_gamescore"."user_id"
     ORDER BY "myapp_gamescore"."score" DESC
   ) AS "rank"
FROM "myapp_gamescore"