CODEWITHSUNDEEP
djangodjango-rest-framework
kachus22
kachus22

Reputation: 439

Django - Sum related fields that belongs to the same FK, for every object

So I'm trying to get the sum of every score in all games related to the according player and how many game he has played

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

class Game(models.Model):
     points = models.IntegerField()
     user = models.ForeignKey(User, on_delete=models.CASCADE)

My desired output is:

{
    "name": "asdf",
    "total_points": "9999"
    "games_played": "12"
}

I thought I could do it getting a value list of my players then looking the sum of all games and count games played for every value on the list, but I don't know how to do it.

I know how to do it for an specific Player, but I want to get the entire list. I see this as a loop where every Player counts by its own.

Thanks

Upvotes: 4

Views: 3711

Answers (2)

JPG
JPG

Reputation: 88569

Your models.py seems like incomplete, So I'm assuming a there is a FK relationship between Player and Game as below

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


class Game(models.Model):
    points = models.IntegerField()
    user = models.ForeignKey(Player, on_delete=models.CASCADE)


You can fetch all summary of your DB by using a Group By query as below,

from django.db.models import Count, Sum, F

Game.objects.annotate(name=F('user__name')).values('name').annotate(games_played=Count('id'), total_points=Sum('points'))



Sample output will be like this,

 <QuerySet [{'name': 'name_1', 'games_played': 2, 'total_points': 6}, {'name': 'name_2', 'games_played': 1, 'total_points': 2}]>


QuerySet is a iterable similar to list. You can iterate over it if you want. Hence each of the item from the QuerySet be like this,

{'name': 'name_1', 'games_played': 2, 'total_points': 6}

Upvotes: 8

mattjegan
mattjegan

Reputation: 2884

Assuming that Player has a User stored as .user, you could do something like:

from django.db.models import Sum

games = Game.objects.filter(user=player.user)
output = {
    'name': player.name,
    'total_points': games.aggregate(Sum('points')),
    'total_games': games.count()
}

Upvotes: 3

Related Questions