[SOLVED] Why and how do params in annotate affect each other?

Why and how do params in annotate affect each other?

I've been reading https://docs.djangoproject.com/en/1.11/topics/db/aggregation/ a lot, but I'm still missing something.

Using Django 1.11, say I have the following models:

class School(models.Model):
    pass
    
class Classroom(models.Model):
    school = models.ForeignKey(School, on_delete=models.PROTECT)
    active = models.BooleanField()
    busy = models.BooleanField()
    
class Chalkboard(models.Model):
    classroom = models.ForeignKey(Classroom, on_delete=models.PROTECT)
    
class Whiteboard(models.Model):
    classroom = models.ForeignKey(Classroom, on_delete=models.PROTECT)

And I create a school, with a classroom, which has 3 whiteboards and 2 chalkboards:

s = School()
s.save()

c = Classroom(school=s, active=True, busy=False)
c.save()

Chalkboard(classroom=c).save()
Chalkboard(classroom=c).save()

Whiteboard(classroom=c).save()
Whiteboard(classroom=c).save()
Whiteboard(classroom=c).save()

I want a summary of how many chalkboards there are at each school that is active but not busy.

q = School.objects.filter(
    Q(classroom__active=True) & Q(classroom__busy=False)
).annotate(
    chalkboard_count=Count('classroom__chalkboard'),
)

q[0].chalkboard_count
2                        # as expected

Now I want to know about chalkboards and whiteboards.

q = School.objects.filter(
    Q(classroom__active=True) & Q(classroom__busy=False)
).annotate(
    chalkboard_count=Count('classroom__chalkboard'),
    whiteboard_count=Count('classroom__whiteboard'), # added this line
)

q[0].chalkboard_count
6                        # expected 2
q[0].whiteboard_count
6                        # expected 3

If I chain the calls to annotate, I get the same result.

q = School.objects.filter(
    Q(classroom__active=True) & Q(classroom__busy=False)
).annotate(
    chalkboard_count=Count('classroom__chalkboard')
).annotate(
    whiteboard_count=Count('classroom__whiteboard')
)

q[0].chalkboard_count
6                        # expected 2
q[0].whiteboard_count
6                        # expected 3

All the while, the counts are what I expect

Chalkboard.objects.count()
2
Whiteboard.objects.count()
3

What am I doing wrong here?

Solution

From the link you posted:

Combining multiple aggregations

Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries: For most aggregates, there is no way to avoid this problem, however, the Count aggregate has a distinct parameter that may help:

Book.objects.annotate(
    Count('authors', distinct=True), 
    Count('store', distinct=True)
)