#32032 closed New feature (duplicate)
Aggregate on union of querysets generates invalid SQL
Reported by: | Jaap Roes | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.1 |
Severity: | Normal | Keywords: | aggregate union |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
I'm getting the following error when trying to perform an aggregate on a union of querysets:
django.db.utils.ProgrammingError: column "__col1" does not exist LINE 1: SELECT COALESCE(BOOL_OR("__col1"), false), COALESCE(BOOL_OR(...
What I have are the following models:
class Organisation(models.Model): ... class Partner(models.Model): ... class Entity(models.Model): organisation = models.ForeignKey(Organisation) partner = models.ForeignKey(Partner, null=True)
A user can have certain permissions on Entities, either directly or through an Organisation or Partner.
class BaseEntityPermissions(models.Model): user = models.ForeignKey(User) can_discombobulate = models.BooleanField(default=True) can_frobnicate = models.BooleanField(default=False) class Meta: abstract = True class EntityPermissions(BaseEntityPermissions): entity = models.ForeignKey(Entity) class Meta: unique_together = ('user', 'entity') class OrganisationEntityPermissions(BaseEntityPermissions): organisation = models.ForeignKey(Organisation) class Meta: unique_together = ('user', 'organisation') class PartnerEntityPermissions(BaseEntityPermissions): partner = models.ForeignKey(Partner) class Meta: unique_together = ('user', 'partner')
These permissions stack; for example, a user can get the permission to "discombobulate" all Entities of a specific Organisation on the Organisation level and can gain the permission to "frobnicate" a specific Entity.
I therefore wrote this function to get the applicable permissions for a user and an entity:
def get_user_entity_permissions(user, entity): querysets = [ EntityPermissions.objects.filter( entity_id=entity.id, user=user ).values('can_discombobulate', 'can_frobnicate'), OrganisationEntityPermissions.objects.filter( organisation_id=entity.organisation_id, user=user ).values('can_discombobulate', 'can_frobnicate') ] if entity.partner_id: querysets.append( PartnerEntityPermissions.objects.filter( partner_id=entity.partner_id, user=user ).values('can_discombobulate', '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) )
Sadly, execution this code results in the error previously mentioned.
The full generated SQL is as follows:
SELECT COALESCE(BOOL_OR("__col1"), false), COALESCE(BOOL_OR("__col2"), false) FROM ( ( SELECT "foo_entitypermissions"."can_discombobulate", "foo_entitypermissions"."can_frobnicate" FROM "foo_entitypermissions" WHERE ("foo_entitypermissions"."user_id" = 1 AND "foo_entitypermissions"."entity_id" = 1) ) UNION ( SELECT "foo_organisationentitypermissions"."can_discombobulate", "foo_organisationentitypermissions"."can_frobnicate" FROM "foo_organisationentitypermissions" WHERE ("foo_organisationentitypermissions"."user_id" = 1 AND "foo_organisationentitypermissions"."organisation_id" = 1) ) ) subquery
This is almost correct, except for the __col1
and __col2
aliases, which don't exist.
Change History (5)
comment:2 by , 4 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Type: | Bug → New feature |
As 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.
comment:3 by , 4 years ago
I did read that in the docs, and I had a different version of the function before that tried to uses .values
on the union queryset before calling .aggregate
:
def get_entity_permissions(user, entity): querysets = [ EntityPermissions.objects.filter( entity_id=entity.id, user=user ), OrganisationEntityPermissions.objects.filter( organisation_id=entity.organisation_id, user=user ) ] if entity.partner_id: querysets.append( PartnerEntityPermissions.objects.filter( partner_id=entity.partner_id, user=user ) ) qs = functools.reduce(lambda qs1, qs2: qs1.union(qs2), querysets) return qs.values('can_discombobulate', 'can_frobnicate').aggregate( can_discombobulate=Coalesce(BoolOr('can_discombobulate'), False), can_frobnicate=Coalesce(BoolOr('can_frobnicate'), False) )
Calling this raises the following error:
django.core.exceptions.FieldError: Cannot resolve keyword '' into field. Choices are: can_discombobulate, can_frobnicate, user, user_id, id, entity, entity_id
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.
Hopefully this doesn't get "fixed" in a way similar to #27995 as that would most likely break my workaround.
follow-up: 5 comment:4 by , 4 years ago
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.
comment:5 by , 4 years ago
Replying to 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.
OK, that wasn't clear at all from your initial message.
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.
OK, good to know, that also wasn't clear. I now feel better for commiting the workaround :-)
I've been able to work around this issue by manually creating aliases for the colums I want to aggregate: