Update Django model based on the row number of rows produced by a subquery on the same model

Question

I have a PostgreSQL UPDATE query which updates a field (global_ranking) of every row in a table, based on the ROW_NUMBER() of each row in that same table sorted by another field (rating). Additionally, the update is partitioned, so that the ranking of each row is relative only to those rows which belong to the same language.

In short, I'm updating the ranking of each player in a game, based on their current rating.

The PostgreSQL query looks like this:

UPDATE stats_userstats
SET    
    global_ranking = sub.row_number
FROM  (
    SELECT id, ROW_NUMBER() OVER (
        PARTITION BY language
        ORDER BY rating DESC
    ) AS row_number
    FROM stats_userstats
) sub
WHERE stats_userstats.id = sub.id;

I'm also using Django, and it'd be fun to learn how to express this query using the Django ORM, if possible.

At first, it seemed like Django had everything necessary to express the query, including the ability to use PostgreSQL's ROW_NUMBER() windowing function, but my best attempt updates all rows ranking with 1:

from django.db.models import F, OuterRef, Subquery
from django.db.models.expressions import Window
from django.db.models.functions import RowNumber

UserStats.objects.update(
    global_ranking=Subquery(
        UserStats.objects.filter(
            id=OuterRef('id')
        ).annotate(
            row_number=Window(
                expression=RowNumber(),
                partition_by=[F('language')],
                order_by=F('rating').desc()
            )
        ).values('row_number')
    )
)

I used from django.db import connection; print(connection.queries) to see the query produced by that Django ORM statement, and got this vaguely similar SQL statement:

UPDATE "stats_userstats" 
SET "global_ranking" = (
    SELECT ROW_NUMBER() OVER (
        PARTITION BY U0."language"
        ORDER BY U0."rating" DESC
    ) AS "row_number" 
FROM "stats_userstats" U0 
WHERE U0."id" = "stats_userstats"."id"

It looks like what I need to do is move the subquery from the SET portion of the query to the FROM, but it's unclear to me how to restructure the Django ORM statement to achieve that.

Any help is greatly appreciated. Thank you!

Answer 1

Subquery filters qs by provided OuterRef. You're always getting 1 as each user is in fact first in any ranking if only them are considered.

A "correct" query would be:

UserStats.objects.alias(
    row_number=Window(
        expression=RowNumber(),
        partition_by=[F('language')],
        order_by=F('rating').desc()
    )
).update(global_ranking=F('row_number'))

But Django will not allow that:

django.core.exceptions.FieldError: Window expressions are not allowed in this query

Related Django ticket: https://code.djangoproject.com/ticket/25643

I think you might comment there with your use case.