#19415: Clarify how aggregates work with multi-valued relationships and multiple
filter() calls
--------------------------------------+------------------------------------
Reporter: svniemeijer@… | Owner:
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
--------------------------------------+------------------------------------
Comment (by benred42):
That does change the results. Using the same setup I used above but
adding in `Count('...', distinct=True)` results in the following:
{{{
for i in qs1:
print(i, i.num_book)
>>> a4 1
print(qs1.query)
>>> SELECT "queries_author"."id", "queries_author"."name",
"queries_author"."num", COUNT(DISTINCT "queries_book_authors"."book_id")
AS "num_book" FROM "queries_author" INNER JOIN "queries_book_authors" ON
("queries_author"."id" = "queries_book_authors"."author_id") INNER JOIN
"queries_book" ON ("queries_book_authors"."book_id" = "queries_book"."id")
WHERE ("queries_book"."pages" < 200 AND "queries_book"."pages" > 100)
GROUP BY "queries_author"."id", "queries_author"."name"
for i in qs2:
print(i, i.num_book)
>>> a4 2
print(qs2.query)
>>> SELECT "queries_author"."id", "queries_author"."name",
"queries_author"."num", COUNT(DISTINCT "queries_book_authors"."book_id")
AS "num_book" FROM "queries_author" INNER JOIN "queries_book_authors" ON
("queries_author"."id" = "queries_book_authors"."author_id") INNER JOIN
"queries_book" ON ("queries_book_authors"."book_id" = "queries_book"."id")
INNER JOIN "queries_book_authors" T4 ON ("queries_author"."id" =
T4."author_id") INNER JOIN "queries_book" T5 ON (T4."book_id" = T5."id")
WHERE ("queries_book"."pages" < 200 AND T5."pages" > 100) GROUP BY
"queries_author"."id", "queries_author"."name"
}}}
So, this does appear to be better, but I still don't think it is giving a
result that is helpful or intuitive. It looks to me like the second query
is counting the same book as the first, just twice. What it does not
appear to be doing is giving a list of Authors with books having either
fewer than 200 pages or greater than 100 pages, nor is it giving a list of
Authors with books having between 100 and 200 pages.
--
Ticket URL: <https://code.djangoproject.com/ticket/19415#comment:18>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/077.5409424e0bc8cb382e281de8269200a2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
