Pure way to use postgresql window functions in django ORM?

Question

I would like to use postgresql window functions like rank() and dense_rank in some queries that need to be done in Django. It works for me in raw SQL, but I'm not sure how to do it in ORM.

A simplified expression looks like this:

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

How do you do this in ORM?

At some point, I may also need to add a section: |

(we use Django 1.9 in Python 3 and already depend on the functions of django.contrib.postgres)

Answer 1

Since Django 2.0 is built into ORM. See https://docs.djangoproject.com/fr/2.0/ref/models/database-functions/#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 several ways to do 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 the GameScore.objects.filter (...) function. extra (). Since this is an old API that at some point should be deprecated, it should only be used if you cannot express your request using other query set methods ... but it still works. Example:

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

So you can add a section, dense rank, ... no problem:

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

And you can access data like:

 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 Func offers the expression Django 1.8 ( https://docs.djangoproject.com/en/1.10/ref/models/expressions/#func-expressions ), which allows you to do this in a 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 encapsulated this in an independent django-rank-query application: https://pypi.python.org/pypi/django-rank-query

There is also a built-in Django implementation of window functions on the way: code.djangoproject.com/ticket/26608

Answer 4

I assume that since you want to use the ORM query, you have a model setting for your table "game_score". In this case, you can use the .raw() request.

Documentation

Using

 sql = """ SELECT id, user_id, score, RANK() OVER(ORDER BY score DESC) AS rank FROM game_score """ game_scores = GameScore.objects.raw(sql) for game_score in game_scores: print game_score.id, game_score.rank, game_score.score 

The rank attribute is called an annotation. More here