Django annotation has side effect on another annotation

Question

I'm running into an issue where adding an annotation to a QuerySet changes the results of previous annotations.

Here is the (simplified) setup:

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

class Unit(models.Model):
    player = models.ForeignKey(Player, on_delete=models.CASCADE,
                               related_name='unit_set')

    rarity = models.IntegerField()

class Gear(models.Model):
    pass

class UnitGear(models.Model):
    unit = models.ForeignKey(Unit, on_delete=models.CASCADE,
                             related_name='ug_set')
    gear = models.ForeignKey(Gear, on_delete=models.PROTECT)

Annotating players with the count of rarity 7 units works well:

Player.objects.annotate(
    rarity7_count=Count(unit_set__rarity=7)
).values_list('name', 'rarity7_count')

[('Player1', 170),
 ('Player2', 172),
 ('Player3', 164),
 ...,
)]

The values returned for rarity7_count above are correct.

If I add the following additional annotation, this is no longer the case:

Player.objects.annotate(
    rarity7_count=Count(unit_set__rarity=7),
    gear_count=Count(unit_set__ug_set)
).values_list('name', 'rarity7_count', 'gear_count')

[('Player1', 476, 456),
 ('Player2', 490, 466),
 ('Player3', 422, 433),
 ...,
)]

Notice how rarity7_count values have changed -- these values are no longer correct! Values for gear_count are, however, correct.

Why is that? How can get both annotation to work without interfering with each other? I have tried all sorts of things and am currently at loss on how to do this.

Answer 1

Yes, since now there are two JOINs, and since the Count(..) [Django-doc] is the number of rows, it will thus act as some sort of multiplier.

We can however solve this by specifying distinct=True, like:

Player.objects.annotate(
    rarity7_count=Count('unit_set', distinct=True, filter=Q(unit_set__rarity=7)),
    gear_count=Count('unit_set__ug_set')
).values_list('name', 'rarity7_count', 'gear_count')

Note that if you want the gear_count to also filter on the rarity, you need to specify the filter= part again.