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"

Answer 2

There are a few ways of doing this:

1) Using annotate and RawSQL(). Preferred method. Example:

from django.db.models.expressions import RawSQL
GameScore.objects.filter().annotate(rank=RawSQL("RANK() OVER(ORDER BY score DESC)", [])   )

2) Using GameScore.objects.filter(...).extra() function. As this is an old API that it is aimed to be deprecated at some point in the future, it should be used it only if you cannot express your query using other queryset methods... but it still works. Example:

GameScore.objects.filter().extra(select={'rank': 'RANK() OVER(ORDER BY score DESC)' )

In this way, you are able to add partitioning, dense rank, ... without any problem:

RawSQL("RANK() OVER(PARTITION BY user_id ORDER BY score DESC")

And you can access to the data as:

game_scores = GameScore.objects.filter().extra(select={'rank': 'RANK() OVER(ORDER BY score DESC)' )

for game_score in game_scores:
    print game_score.id, game_score.rank, game_score.score

1) https://docs.djangoproject.com/es/1.9/ref/models/querysets/#annotate

2) https://docs.djangoproject.com/es/1.9/ref/models/querysets/#extra

Answer 3

Since offers Func expression Django 1.8 (https://docs.djangoproject.com/en/1.10/ref/models/expressions/#func-expressions), which allows to do this much cleaner and more reusable way:

class Rank(Func):
    function = 'RANK'
    template = '%(function)s() OVER (ORDER BY %(expressions)s DESC)'

GameScore.objects.annotate(rank=Rank('score'))

I have encapsulated this to independent app django-rank-query: https://pypi.python.org/pypi/django-rank-query

There is also native Django implementation of window functions on the way: code.djangoproject.com/ticket/26608