Re: [Django] #32032: Aggregate on union of querysets generates invalid SQL
Django
-------------------------------------+-------------------------------------
Reporter: Jaap Roes | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: aggregate union | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jaap Roes):
I've been able to work around this issue by manually creating aliases for
the colums I want to aggregate:
{{{
def get_entity_permissions(user, entity):
querysets = [
EntityPermissions.objects.filter(
entity_id=entity.id, user=user
).values(
_can_discombobulate=F('can_discombobulate'),
_can_frobnicate=F('can_frobnicate')
),
OrganisationEntityPermissions.objects.filter(
organisation_id=entity.organisation_id, user=user
).values(
_can_discombobulate=F('can_discombobulate'),
_can_frobnicate=F('can_frobnicate')
]
if entity.partner_id:
querysets.append(
PartnerEntityPermissions.objects.filter(
partner_id=entity.partner_id, user=user
).values(
_can_discombobulate=F('can_discombobulate'),
_can_frobnicate=F('can_frobnicate')
)
)
qs = functools.reduce(lambda qs1, qs2: qs1.union(qs2), querysets)
return qs.aggregate(
can_discombobulate=Coalesce(BoolOr('_can_discombobulate'), False),
can_frobnicate=Coalesce(BoolOr('_can_frobnicate'), False)
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32032#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Jaap Roes | Owner: nobody
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: aggregate union | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* type: Bug => New feature
* resolution: => duplicate
Comment:
As [https://docs.djangoproject.com/en/stable/ref/models/querysets/#union
per the documentation], ''"only LIMIT, OFFSET, COUNT(*), ORDER BY, and
specifying columns (i.e. slicing, count(), order_by(), and
values()/values_list()) are allowed on the resulting QuerySet."''. See
#27995 for a ticket to raise a helpful message about this and also a
ticket #28519 to support these features.
Duplicate of #28519.
--
Ticket URL: <https://code.djangoproject.com/ticket/32032#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Jaap Roes | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: aggregate union | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
> Sad to see this closed as a duplicate of #28519. I feel this ticket is
much more narrowly scoped and Django seems to be almost capable of doing
the right thing so it seems like an easier fix/addition.
Closing as a duplicate doesn't changes much, we can still add support only
for `aggregate()` as a part of #28519.
> Hopefully this doesn't get "fixed" in a way similar to #27995 as that
would most likely break my workaround****
That's why I didn't propose to raise an error.
--
Ticket URL: <https://code.djangoproject.com/ticket/32032#comment:4>