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.

Yes, since you make two `JOIN`s, this will act as some sort of "multiplier". — Willem Van Onsem, Jun 11 '19 at 20:32
@WillemVanOnsem: can I find some documentation on this (at Django level)? How can I achieve what I intend to do here with a single QuerySet? — abey, Jun 11 '19 at 20:38

score 5 · Accepted Answer · answered Jun 11 '19 at 20:36

5

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.

answered Jun 11 '19 at 20:36

Willem Van Onsem

443,496
30
428
555

Thanks a lot -- this works. I have a follow up question: I'm now trying to add a `Sum` to this annotation: `r7_sum=Sum('unit_set__rarity', filter=Q(unit_set__rarity=7))` (reality is less trivial, but I'm adapting for the models above). With `Sum` I get a result much too large (same problem as above I assume), and with `DistinctSum` from this [answer](https://stackoverflow.com/a/54278155/229511), the result is too low. What am I missing? Should I write a new question? – abey Jun 12 '19 at 08:02
1

@abey: it is possible that the `filter`ing is relevant here, since for a filter, you frequently add extra constraints that something is non-nullable, so I propse that you work out a MWE that can reproduce the error. – Willem Van Onsem Jun 12 '19 at 08:33
Done, here is [the question](https://stackoverflow.com/questions/56567841/django-count-and-sum-annotations-interfere-with-each-other) – abey Jun 12 '19 at 17:52

Django annotation has side effect on another annotation

1 Answers1