multiple Django annotate Count over reverse relation of a foreign key with an exclude returns a strange result (18)

Question

The strangest thing, either I'm missing something basic, or maybe a django bug

for example:

class Author(Model):
       name = CharField()

class Parent(Model):
       name = CharField(

class Subscription(Model):
      parent = ForeignKey(Parent, related_name='subscriptions')

class Book(Model):
      name = CharField()
      good_book = BooleanField()
      author = ForeignKey(Author, related_name='books')


class AggregatePerson(Model):
     author =  OneToOneField(Author, related_name='+')
     parent =  OneToOneField(Parent, related_name='+')

when I try:

AggregatePerson.objects.annotate(counter=Count('author__books')).order_by('counter')

everything work correctly. both ordering and fields counter and existing_subs show the correct number BUT if I add the following:

AggregatePerson.objects.annotate(existing_subs=Count('parent__subscriptions')).exclude(existing_subs=0).annotate(counter=Count('author__books')).order_by('counter')

Then counter and existing_subs fields become 18

Why 18? and what am I doing wrong?

Thanks for the help!

EDIT clarification after further research:

18. is the number of parent__subscriptions, the code breaks even without the exclude, **for some reason counter also gets the value of existing_subs

Answer 1

I found the answer to this issue.

Tl;dr:

You need to add distinct=True inside the Count like this:

AggregatePerson.objects.annotate(counter=Count('author__books', distinct=True))

Longer version:

Adding a Count annotation is adding a LEFT OUTER JOIN behind the scene. Since we add two annotations, both referring to the same table, the number of selected and grouped_by rows is increased since some rows may appear twice (once for the first annotation and another for the second annotation) because LEFT OUTER JOIN allows empty cells (rows) on select from the right table.