[Fixed]-How do I get the position of a result in the list after an order_by?

by

17👍

✅

I don’t think you can do this in one database query using Django ORM. But if it doesn’t bothers you, I would create a custom method on a model:

from django.db.models import Count

class Model(models.Model):
    score = models.IntegerField()
    ...

    def ranking(self):
        count = Model.objects.filter(score__lt=self.score).count()
        return count + 1

You can then use "ranking" anywhere, as if it was a normal field:

print Model.objects.get(pk=1).ranking

Edit: This answer is from 2010. Nowadays I would recommend Carl’s solution instead.

👤Ludwik Trammer

13👍

Using the new Window functions in Django 2.0 you could write it like this…

from django.db.models import Sum, F
from django.db.models.expressions import Window
from django.db.models.functions import Rank

Model.objects.filter(name='searching_for_this').annotate(
            rank=Window(
                expression=Rank(),
                order_by=F('score').desc()
            ),
        )
👤Carl

3👍

Use something like this:

obj = Model.objects.get(name='searching_for_this')
rank = Model.objects.filter(score__gt=obj.score).count()

You can pre-compute ranks and save it to Model if they are frequently used and affect the performance.

👤Harut

2👍

In “raw SQL” with a standard-conforming database engine (PostgreSql, SQL Server, Oracle, DB2, …), you can just use the SQL-standard RANK function — but that’s not supported in popular but non-standard engines such as MySql and Sqlite, and (perhaps because of that) Django does not “surface” this functionality to the application.

👤Alex Martelli

Leave a comment