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

Note that you could also just compute the rank in Python since this is only for display purposes. — Kevin Christopher Henry, Mar 20 '19 at 12:24
I will do this if I will not be able to get expected result with a database query. — umat, Mar 20 '19 at 12:55
The problem which occurred in this question was caused by using SQLite db backend. After I switched to Postgres, the code started working as expected. — umat, Jul 14 '19 at 11:57

score 4 · Answer 1 · edited Jun 14 '19 at 07:50

4

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.

edited Jun 14 '19 at 07:50

Enderson Menezes

459
3
9

answered Mar 20 '19 at 12:56

Endre Both

5,540
1
26
31

Before asking the question I was using sqlite backend. Now I've also tested it on Postgres and the query from my original question works as expected! That's strange because it seems that sqlite [should support window functions](https://www.sqlite.org/windowfunctions.html). – umat Mar 20 '19 at 14:54
2

My query also seems to work on PG only, not SQLite, even though it executes fine in the SQLite shell. Window functions are quite new and possibly not 100% working as yet. – Endre Both Mar 20 '19 at 16:08

Django ORM: create ranking based on related model count

1 Answers1