Finding the Position of a certain record in the Query in DJango ORM

I have a model of Django like this,

class Account(models.Model):
    user = models.OneToOneField(User, null=True, on_delete=models.CASCADE)
    name = models.CharField(max_length=255, null=True)
    ratings = models.FloatField(default=1000)

What I want to do is the person who's logged in, I want to find his rank based upon the user's ratings. So, that is, I need to find the Position of the specified row in a query where the records will be ordered by the ratings column, in descending order.

I found a good solution here but it is unfortunately not working for me. https://stackoverflow.com/a/51317266/11454905

Using this same concept, I typed this query,

player = Account.objects.filter(user = request.user).annotate(rank = Window(expression=Rank(), order_by=F('ratings').desc()),)[0]

But no matter what user is, it always returns player.rank as 1, which is not true. I fail to understand what am I doing wrong and how can I correct it.

If you are doing this for just a single User object, then you can just count the Users that have a rating higher than the current user - I note that you are using filter(user=request.user) which doesn't actually get a single user object. Generally speaking, you should use get(), although here you can just do request.user.

rank = Player.objects.filter(ratings__gte=request.user.ratings).count()

As to annotating a queryset of users, you can use Window() and DenseRank() expressions:

from django.db.models import Window
from django.db.models.functions import DenseRank

players = Player.objects.order_by('ratings').annotate(
    rank=Window(
        expression=DenseRank(),
        order_by=F('ratings').desc()
    )
)
Back to Top