3

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:

  1. is the number of parent__subscriptions, the code breaks even without the exclude, **for some reason counter also gets the value of existing_subs
alonisser
  • 11,542
  • 21
  • 85
  • 139
  • Your last code line in fact must be `AggregatePerson.objects.annotate(existing_subs=Count('parent__subscriptions')).exclude(existing_subs=0).annotate(counter=Count('author__books')).order_by('counter')`: replace dot to `__` and add `'`, right? – stalk Oct 06 '14 at 13:25
  • True, a type here, the code is correct.. fixing – alonisser Oct 06 '14 at 13:27
  • I suppose the reason of strange result is because of [Order of annotate() and filter() clauses](https://docs.djangoproject.com/en/dev/topics/db/aggregation/#order-of-annotate-and-filter-clauses) – stalk Oct 06 '14 at 13:30
  • I also guess so, but I need to filter on the annotation. If I can't do that, then I don't need it.. – alonisser Oct 06 '14 at 13:31
  • you can filter on the annotation, but maybe you need to apply annotation before the filtering, not after. In you case, before `exclude` – stalk Oct 06 '14 at 13:33
  • thanks @stalk I tried that.. didn't work (this of course not our production code, just a simplfied example for the problem) Also see the update – alonisser Oct 06 '14 at 19:32
  • maybe this is related answer: http://stackoverflow.com/a/11317122/821594 – stalk Oct 06 '14 at 20:12

2 Answers2

3

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.

pawel
  • 513
  • 8
  • 15
alonisser
  • 11,542
  • 21
  • 85
  • 139
  • I've added distinct as you mentioned, now i'm getting an AttributeError, which says: bool' object has no attribute 'resolve_expression' – Yuri Heupa Mar 30 '16 at 13:59
  • `distinct=True` should be an argument for `Count` and not for `annotate` – pawel Nov 14 '18 at 12:56
1

(repeating essentials of my reply in another forum)

This looks like a Django bug. Possible workarounds:

1) Add the two annotations in one annotate() call: ...annotate(existing_subs=Count('parent__subscriptions'),counter=Count('author__books'))...

2) Replace the annotation for existing_subs and exclude(existing_subs=0) with an exclude (parent__subscriptions=None).

Shai Berger
  • 2,963
  • 1
  • 20
  • 14
  • I already tried the first suggestion and it doesn't work. The second suggestion does the work for me, but doesn't explain this strange bug – alonisser Oct 13 '14 at 11:24