Re: [Django] #34145: Explicit GROUPing by aggregate is not supported (was: It is currently not possible to group by annotated fields)
Django
-------------------------------------+-------------------------------------
Reporter: Karolis Ryselis | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* cc: Simon Charette (added)
* resolution: => needsinfo
* status: new => closed
* component: Uncategorized => Database layer (models, ORM)
Comment:
> I would expect this queryset to either group by tags...
I think you'll have to provide more details on what kind of SQL you are
try to produce as it's not possible to `GROUP BY` an aggregate function
and it's not clear from the example you've provided what exactly you are
trying to achieve.
Are you looking to use a subquery?
{{{#!python
PackingItem.objects.values(
min_packing_tags_title=PackingTag.objects.values(
"packings"
).filter(packings=OuterRef("packing")).values_list(
Min("title"),
)
).annotate(
total_amount=Sum('amount')
)
}}}
Which should generate something along the lines of
{{{#!sql
SELECT
(
SELECT MIN(title)
FROM packing_tag
LEFT JOIN packing_tags ON (packing_tag.id = packing_tags.tag_id)
WHERE packing_tags.packing_id = packtingitem.packing_id
GROUP BY packing_tags.packing_id
) AS min_packing_tags_title,
SUM(amount) AS sum_amount
FROM packtingitem
GROUP BY min_packing_tags_title
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34145#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.