how to order Django query based on the sum of a specific field grouped by another field?

• The Model is simply recording goals that players score, every player has many inputs that each refers to a single goal.
• I'm trying to find out the top 3 scorers with the highest amount of goals scored.

Here is my model :

class Goal(models.Model):
    match = models.ForeignKey(Match,on_delete=models.CASCADE)
    date_time = models.DateTimeField()
    team = models.ForeignKey("teams.Team",on_delete=models.PROTECT,null=True,blank=True)
    player = models.ForeignKey('players.PlayerProfile',related_name="goal_maker",on_delete=models.PROTECT,null=True,blank=True)
    assistant = models.ForeignKey('players.PlayerProfile',related_name="goal_assist",on_delete=models.PROTECT,null=True,blank=True)

Now what I want to do is to group it by player field ordering it from the highest player with goals to the lowest one

What I have tried

I have tried to loop trough all my database entries for this model Goal.objects.all() and check if it is the same player I do =1 if not I create a new entry, and that is surly not the best way do achieve this.

CodePudding user response：

You can annotate the PlayerProfiles with the umber of goals, and then order by that number and slice, so:

from django.db.models import Count

PlayerProfile.objects.annotate(
    total_goals=Count('goal_maker')
).order_by('-total_goals')[:3]