Django ORM: create ranking based on related model count

Question

I have 2 models in my database: Movie and Comment (it has a foreign key pointing to Movie).

from django.db import models


class Movie(models.Model):
    title = models.CharField(max_length=120, blank=False, unique=True)

class Comment(models.Model):
    movie = models.ForeignKey(
        movie_models.Movie, null=False, on_delete=models.CASCADE
    )
    body = models.TextField(blank=True)

What I am trying to do is create a ranking of Movies where the position is determined by the number of related Comments.

I can annotate number of comments and order the result properly:

from django.db.models import Count

qs = Movie.objects.annotate(
    total_comments=Count('comment')
).order_by('-total_comments')

This gives me queryset in correct order but I would like to do one more thing - annotate the 'rank' to each row.

So my question is: how can I annotate 'rank' to each row of the result? Note that it is required for movies with the same amount of comments to have the same rank.

|  movie_title | total_comments | rank |
|--------------|----------------|------|
| mov1         | 10             | 1    |
| mov2         | 5              | 2    |
| mov3         | 5              | 2    |
| mov4         | 3              | 3    |

I tried to use window functions, as some of the examples seemed legit for me:

from django.db.models import F, Window

dense_rank = Window(
    expression=DenseRank(),
    order_by=F('total_comments').desc(),
)

qs = Movie.objects.annotate(
    total_comments=Count('comment')
).annotate(rank=dense_rank)

But running this query raises django.db.utils.OperationalError: near "(": syntax error

Answer 1

RowNumber should be sufficient if the query is appropriately ordered:

from django.db.models import Count
from django.db.models.expressions import F, Window
from django.db.models.functions.window import RowNumber

qs = (Movie.objects
    .annotate(total_comments=Count('comment'))
    .order_by('total_comments')
    .annotate(rank = Window(expression=RowNumber())
)

But since the row number is inherent in the sequence of the records, Kevin is right in suggesting an easy Python method (e.g. enumerate) as you're going to be iterating over the recordset at some point anyway.

It would be different if you wanted a different ordering for your recordset than for the ranking, then it would make sense to use Window with a separate order_by parameter.

I'm not sure if RowNumber is supported by all backends.