Opened 7 years ago

Last modified 2 years ago

#10045 new Cleanup/optimization

Improve documentation of .annotate() / .filter() ordering quirks

Reported by: alex@… Owned by:
Component: Documentation Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by russellm)

The documentation surrounding the ordering of .annotate() and .filter() clauses needs improvement. The current documentation is confusing (and arguably incorrect), leading to invalid bug reports like the following:

---

I get something that looks like a missing join condition when doing

ModelA.objects.all().annotate(num_b=Count('modelb')).filter(modelb__somebool=True)[0].num_b

I get num_b = 2^i for i objects of ModelB instead of num_b=i as I would expect to.
Please see attached models.py file for the Model definition and test case.

django revision: 9756

---

Attachments (2)

models.py (709 bytes) - added by alex@… 7 years ago.
test_10045.tar.gz (6.4 KB) - added by SmileyChris 4 years ago.
self-contained simple project - run manage.py test

Download all attachments as: .zip

Change History (9)

Changed 7 years ago by alex@…

comment:1 Changed 7 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

I think you may have missed this section of the docs:

There is a difference between:

ModelA.objects.all().annotate(num_b=Count('modelb')).filter(modelb__somebool=True)[0].num_b

which evaluates as the SQL:

SELECT "myapp_modela"."id", "myapp_modela"."name", COUNT("myapp_modelb"."id") AS "num_b" FROM "myapp_modela" LEFT OUTER JOIN "myapp_modelb" ON ("myapp_modela"."id" = "myapp_modelb"."a_id") INNER JOIN "myapp_modelb" T3 ON ("myapp_modela"."id" = T3."a_id") WHERE T3."somebool" = True  GROUP BY "myapp_modela"."id", "myapp_modela"."name"

and the query:

ModelA.objects.all().filter(modelb__somebool=True).annotate(num_b=Count('modelb'))[0].num_b

which evaluates as the SQL:

SELECT "myapp_modela"."id", "myapp_modela"."name", COUNT("myapp_modelb"."id") AS "num_b" FROM "myapp_modela" LEFT OUTER JOIN "myapp_modelb" ON ("myapp_modela"."id" = "myapp_modelb"."a_id") WHERE "myapp_modelb"."somebool" = True  GROUP BY "myapp_modela"."id", "myapp_modela"."name"

Notice the order of the annotate() and filter() clauses. By putting the filter after the annotate, the annotation uses a separate join table. When the annotate follows the filter, the annotation uses the filter's join table.

comment:2 Changed 7 years ago by alex@…

I did read the docs, but apparently didn't understand them: They say: "annotation in the first query will provide the total number of all books published by the publisher;" that would map to the total number of B objects in my example, wouldn't it? And that is not 4. Also: "In the first query, the annotation precedes the filter, so the filter has no effect on the annotation".
Remove the filter, and the expected result is returned:

>>> ModelA.objects.all().annotate(num_b=Count('modelb'))[0].num_b
2

Note that somebool is True on all ModelB objects, so the filter should really have no effect, right?

comment:3 Changed 7 years ago by russellm

  • Component changed from ORM aggregation to Documentation
  • Description modified (diff)
  • Resolution invalid deleted
  • Status changed from closed to reopened
  • Summary changed from bug in aggregations: .annotate() followed by .filter() on related field breaks to Improve documentation of .annotate() / .filter() ordering quirks
  • Triage Stage changed from Unreviewed to Accepted

In this case, I am confirming that the query is returning the correct results. The order of filter() and annotate() is significant, and the result you have received is what should be expected.

However, I will concede that the documentation of this quirk could certainly be improved. I'll reopen this ticket, but I'll repurpose it to clarify the documentation of this feature since there isn't anything at a code level that requires fixing. If you (or anyone else) wants to take a swing at describing this quirk better, feel free to do so.

comment:4 Changed 4 years ago by SmileyChris

  • Severity set to Normal
  • Type set to Cleanup/optimization

comment:5 Changed 4 years ago by anonymous

  • Easy pickings unset
  • UI/UX unset

This seems more than documenting a "quirk". The very example given in the docs is broken.

Changed 4 years ago by SmileyChris

self-contained simple project - run manage.py test

comment:6 Changed 4 years ago by SmileyChris

(anon was me)

In the project test, I create two Publishers, and assign three Books to the first publisher, two above a rating of 3.0. Running Publisher.objects.annotate(num_books=Count('book')).filter(book__rating__gt=3.0)[0].num_books returns 6.

comment:7 Changed 2 years ago by aaugustin

  • Status changed from reopened to new
Note: See TracTickets for help on using tickets.
Back to Top