How to get the position of the result in the list after order_by?

Question

I am trying to find an effective way to find the rank of an object in the database associated with this account. 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 for the database to filter using order_by:

Model.objects.all (). Order_by ('rating'). Filter (name = 'searching_for_this')

But there seems to be no way to get 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-calculate the ranks in the inset, but that seems messy.

Answer 1

I do not think you can do this in a single database query using Django ORM. But if this does not bother you, I would create a custom method for the 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 

Then you can use “ranking” anywhere, as if it were a normal field:

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

Answer 2

In raw SQL with a database-compatible database engine (PostgreSql, SQL Server, Oracle, DB2, ...) you can simply use the SQL-standard RANK function, but not supported in popular but non-standard engines such as MySql and Sqlite, and (possibly because of this) Django does not "impose" this functionality on the application.

Answer 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-calculate the ranks and save them in the Model if they are often used and affect performance.