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.
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.
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()
),
)
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With