Aggregate for multiple instances with a ManyToOne relation

I am new to Django and I want to build a Website to track boardgames.

What I want: For each round played a Player will get points and I want to create a table which shows the sum of all Scores for each Player.

My model is as follows:

class Player(models.Model):
    name = models.CharField(max_length=150)

class Score(models.Model):
    player = models.ForeignKey(Player, blank=False, null=False, on_delete=models.PROTECT)
    scorevalue = models.IntegerField(blank=False, null=False)

I would like to get a queryset with all players where an additional field with the sum of his scores is added. In SQL something like

Select distinct playername, sum(scorevalue) over (partition by playername) 
from Players 
Join Scores

My main problem is to understand the connection between “aggregate” and “annotate” functions in combination with instances and querysets.

With an instance I can get

Player.objects.get(pk=1).score_set.aggregate(score_sum=Sum('scorevalue'))

but is it possible to do this for every instance in a queryset?

Thanks for the help.

Answer

Yes, in that case you annotate the objects, so with:

from django.db.models import Sum

Player.objects.annotate(
    score_sum=Sum('score__scorevalue')
)

The Player objects that arise from this queryset will have an extra attribute .score_sum which is the sum of the related Score records.

One can make use of two consecutive underscores (__) to look “through” relations, and thus access data of related model records.