Proper way to annotate a rank field for a queryset

Question

Assume models like this:

class Person(models.Model):
  name = models.CharField(max_length=20)

class Session(models.Model):
  start_time = models.TimeField(auto_now_add=True)
  end_time = models.TimeField(blank=True, null=True)
  person = models.ForeignKey(Person)

class GameSession(models.Model):
  game_type = models.CharField(max_length=2)
  score = models.PositiveIntegerField(default=0, blank=True)
  session = models.ForeignKey(Session)

I want to have a queryset function to return total score of each person which is addition of all his games score and all times he has spent in all his sessions alongside with a rank that a person has relative to all persons. Something like below:

class DenseRank(Func):
  function = 'DENSE_RANK'
  template = '%(function)s() Over(Order by %(expressions)s desc)'

class PersonQuerySet(models.query.QuerySet):
  def total_scores(self):
    return self.annotate(total_score=some_fcn_for_calculate).annotate(rank=DenseRank('total_score'))

I could find a way to calculate total score, but dense rank is not what I want, because it just calculates rank based on persons in current queryset but I want to calculate rank of a person relative to all persons.

I use django 1.11 and postgres 10.5, please suggest me a proper way to find rank of each person in a queryset because I want to able to add another filter before or after calculating total_score and rank.

mistiru · Answer 1 · 2018-11-15T16:41:26.700

Sadly, it is not a possible operation since (to me) the postgresql WHERE operation (filter/exclude) narrows the rows before the aggregation functions can work on them.

The only solution I found is to simply compute the ranking for all Person with a separate queryset and then, to annotate your queryset with these results.

This answer (see the improved method) explains how to "annotate a queryset with externally prepared data in a dict".

Here is the implementation I made for your models:

class PersonQuerySet(models.QuerySet):
    def total_scores(self):
        # compute the global ranking
        ranks = (Person.objects
                 .annotate(total_score=models.Sum('session__gamesession__score'))
                 .annotate(rank=models.Window(expression=DenseRank(),
                                              order_by=models.F('total_score').decs()))
                 .values('pk', 'rank'))
        # extract and put ranks in a dict
        rank_dict = dict((e['pk'], e['rank']) for e in ranks)

        # create `WHEN` conditions for mapping filtered Persons to their Rank
        whens = [models.When(pk=pk, then=rank) for pk, rank in rank_dict.items()]
        # build the query
        return (self.annotate(rank=models.Case(*whens, default=0,
                                               output_field=models.IntegerField()))
                .annotate(total_score=models.Sum('session__gamesession__score')))

I tested it with Django 2.1.3 and Postgresql 10.5, so the code may lightly change for you.
Feel free to share a version compatible with Django 1.11!

Proper way to annotate a rank field for a queryset

1 Answers1

Linked