Extra conditional annotation breaks previous annotation

Question

I have the following models in a game app I'm working on:-

class Player(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='players')


class Game(models.Model):
    players = models.ManyToManyField(Player, through='GameMembership', related_name='games')


class GameMembership(models.Model):
    game = models.ForeignKey(Game, related_name='memberships')
    player = models.ForeignKey(Player, related_name='memberships')
    team = models.ForeignKey(Team, null=True, related_name='memberships')
    selected = models.BooleanField(default=False)


class Team(models.Model):
    game = models.ForeignKey(Game, related_name='teams')
    score = models.IntegerField(null=True)

I want to get a list of all the Players along with the number of times they were selected, so I annotate the queryset like this:-

Player.objects.all().annotate(played=Count(Case(When(memberships__selected=True, then=1))))

which works exactly as expected.

However, I also want the total number of goals that were scored in all the games each player was selected, so I annotate like this:-

Player.objects.all().annotate(played=Count(Case(When(memberships__selected=True, then=1))), total_goals=Sum(Case(When(memberships__selected=True, then='games__teams__score'))))

which gives the right number for total_goals but for some reason, the value of the played count has doubled!

All I did was add this annotation:-

total_goals=Sum(Case(When(memberships__selected=True, then='games__teams__score')))

So what happened? I strongly suspect I need a distinct() call somewhere but I don't see why the extra annotation has an effect on the first one.

Any ideas?

Extra conditional annotation breaks previous annotation

0 Answers0