Opened 16 years ago

Last modified 9 years ago

#10045 closed

Improve documentation of .annotate() / .filter() ordering quirks — at Version 3

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

Description (last modified by Russell Keith-Magee)

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

---

Change History (4)

by alex@…, 16 years ago

Attachment: models.py added

comment:1 by Russell Keith-Magee, 16 years ago

Resolution: invalid
Status: newclosed

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 by alex@…, 16 years ago

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 by Russell Keith-Magee, 16 years ago

Component: ORM aggregationDocumentation
Description: modified (diff)
Resolution: invalid
Status: closedreopened
Summary: bug in aggregations: .annotate() followed by .filter() on related field breaksImprove documentation of .annotate() / .filter() ordering quirks
Triage Stage: UnreviewedAccepted

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.

Note: See TracTickets for help on using tickets.
Back to Top