Django model - how to add order index annotation?

Question

How can I annotate a queryset in django, to add additional field that will represent an index (place) of each row within the ordered result?

I need to retrieve users in an ordered way with addition of a field that will specify an absolute place of each object within the order.

That's the code I have:

users = User.objects.all().annotate(total_points=Sum('score_earnings', distinct=True)).order_by('-total_points')

users = users.annotate(place, /* how to get an index of each ordered? */)

This is the related Scores model:

class Score(models.Model):
    user = models.ForeignKey('users.CustomUser', related_name='score_earnings', on_delete=models.CASCADE)
    points = models.DecimalField(max_digits=8, decimal_places=1)

So this place field should represent an index of each row in the result: 1, 2, 3, ...

Later I will have to select a range from the result and expect to get the value of place field absolute:

users[3:6]

Expected result:

[{
   "total_points": 100.0,
   "place": 3
}, {
   "total_points": 70.0,
   "place": 4
}, {
   "total_points": 50.0,
   "place": 5
}]

I'm using MySQL DB.

------------Update------------

I was able to do something similar in SQL:

set @row_num = 0; 
SELECT name, @row_num := @row_num + 1 as row_index
FROM Users

But it's still not exactly what I need, because if I add LIMIT statement, row_index value will be within the new result...

Answer 1

I did similar type of work few months earlier. I was using postgres as database and django 2.0. I am not sure following code is going to solve your problem or not. But i want to share my part of code that solve my problem.

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

 ranklist = Score.objects.annotate(
            place=Window(
                expression=DenseRank(),
                order_by=[
                    F('points').desc(),
                ]))