How do I get the position of a result in the list after an order_by?

Question

I'm trying to find an efficient way to find the rank of an object in the database related to it's score. My naive solution looks like this:

rank = 0
for q in Model.objects.all().order_by('score'):
  if q.name == 'searching_for_this'
    return rank
  rank += 1

It should be possible to get the database to do the filtering, using order_by:

Model.objects.all().order_by('score').filter(name='searching_for_this')

But there doesn't seem to be a way to retrieve the index for the order_by step after the filter.

Is there a better way to do this? (Using python/django and/or raw SQL.)

My next thought is to pre-compute ranks on insert but that seems messy.

Answer 1

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.

Answer 2

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.

Answer 3

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):
        aggregate = Model.objects.filter(score__lt=self.score).aggregate(ranking=Count('score'))
        return aggregate['ranking'] + 1

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

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