#32239 closed Bug (needsinfo)
FilteredRelation is not working with GenericRelation
Reported by: | Omar Altayyan | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.1 |
Severity: | Normal | Keywords: | FilteredRelation GenericRelation Generic Relation |
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 have the following models:
class Workspace(Model): pass class Event(Model): workspace = ForeignKey(Workspace) limit = FloatField(default=0) entity_type = models.ForeignKey(ContentType, on_delete=models.CASCADE) entity_id = models.PositiveIntegerField() entity = GenericForeignKey('entity_type', 'entity_id') class Entity(Model): events = GenericRelation(Event, content_type_field='entity_type', object_id_field='entity_id') class Meta: abstract = True class SomeEntity(Entity): pass
Now I need to build a queryset to sort objects of SomeEntity by the limit of their events inside a given Workspace
The SQL query I'm hoping to have is something like this:
SELECT "some_entity"."id", workspace_events."id", COALESCE(workspace_events."limit", 0) AS "limit" FROM "some_entity" LEFT OUTER JOIN "event" workspace_events ON ( "some_entity"."id" = workspace_events."entity_id" AND workspace_events."entity_type_id" = SOME_ENTITY_CONENT_TYPE_ID AND workspace_events."workspace_id" = GIVEN_WORKSPACE_ID ) ) ORDER BY "limit" DESC'
Where:
SOME_ENTITY_CONTENT_TYPE_ID is the content type id of the SomeEntity model.
GIVEN_WORKSPACE_ID is the workspace id I want to filter the events on.
The queryset I'm using is this:
SomeEntity.objects.annotate( workspace_events=FilteredRelation('events', condition=Q(events__workspace_id=GIVEN_WORKSPACE_ID)), limit=Coalesce('workspace_events__limit', 0), ).values('id', 'workspace_events__id', 'limit').order_by('-limit')
But the query I'm getting from the above queryset doesn't include the workspace condition in the join at all!
SELECT "some_entity"."id", workspace_events."id", COALESCE(workspace_events."limit", 0) AS "limit" FROM "some_entity" LEFT OUTER JOIN "event" workspace_events ON ( "some_entity"."id" = workspace_events."entity_id" AND workspace_events."entity_type_id" = SOME_ENTITY_CONENT_TYPE_ID AND ) ) ORDER BY "limit" DESC'
it seems that workspace condition is not being added to the join conditions, so it probably needs a fix.
Change History (7)
comment:1 by , 4 years ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
comment:2 by , 4 years ago
Here is the test/models.py
file:
from django.db import models class Workspace(models.Model): pass class Event(models.Model): workspace = models.ForeignKey(Workspace, on_delete=models.CASCADE) limit = models.FloatField(default=0) entity_type = models.ForeignKey(ContentType, on_delete=models.CASCADE) entity_id = models.PositiveIntegerField() entity = GenericForeignKey('entity_type', 'entity_id') class Entity(models.Model): events = GenericRelation(Event, content_type_field='entity_type', object_id_field='entity_id') class Meta: abstract = True class SomeEntity(Entity): pass
After migrating these models to the database, try running the following code using python manage.py shell
:
>>> from django.db.models import FilteredRelation >>> from django.db.models.functions import Coalesce >>> from test.models import SomeEntity >>> str(SomeEntity.objects.annotate( ... workspace_events=FilteredRelation('events', condition=Q(events__workspace_id=1)), ... limit=Coalesce('workspace_events__limit', 0), ... ).values('id', 'workspace_events__id', 'limit').order_by('-limit').query)
The last command will print this:
'SELECT "test_someentity"."id", workspace_events."id", COALESCE(workspace_events."limit", 0) AS "limit" FROM "test_someentity" LEFT OUTER JOIN "test_event" workspace_events ON ("test_someentity"."id" = workspace_events."entity_id" AND (workspace_events."entity_type_id" = 186)) ORDER BY "limit" DESC'
Notice that the condition on events workspace id specified by the FilteredRelation doesn't have an effect on the resulted query.
comment:3 by , 4 years ago
Resolution: | needsinfo |
---|---|
Status: | closed → new |
I reopened this ticket since I've added more information about the bug in the previous comment
Thanks in advance
follow-up: 6 comment:4 by , 4 years ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
I'm sorry but I don't see any new details, these are exactly the same models as in the ticket description, all works for me on the current master and with Django 3.1:
>>> qs = SomeEntity.objects.annotate( ... workspace_events=FilteredRelation('events', condition=Q(events__workspace_id=1)), ... limit=Coalesce('workspace_events__limit', 0, output_field=IntegerField()), ).values('id', 'workspace_events__id', 'limit').order_by('-limit') >>> print(qs.query) SELECT "ticket_32239_someentity"."id", workspace_events."id", COALESCE(workspace_events."limit", 0) AS "limit" FROM "ticket_32239_someentity" LEFT OUTER JOIN "ticket_32239_event" workspace_events ON ("ticket_32239_someentity"."id" = workspace_events."entity_id" AND (workspace_events."entity_type_id" = 10) AND (workspace_events."workspace_id" = 1)) ORDER BY "limit" DESC
comment:5 by , 4 years ago
Resolution: | needsinfo |
---|---|
Status: | closed → new |
@Mariusz Felisiak
did u try this on the latest stable django release of 3.1.4, because we tried the same exact code u posted without modification and it still ignored the last AND in the left outer join.
could it be something solved in django's master branch and not released yet?
comment:6 by , 4 years ago
Resolution: | → needsinfo |
---|---|
Status: | new → closed |
Please don't reopen this ticket without providing a sample project or new details. Yes, it works for me with Django 3.1.4, see my previous comment.
comment:7 by , 4 years ago
my sincere apologies, thank u for your time, sorry for the inconvenience we have been working hard on this as it's causing us to do some hacks that slow down queries significantly, thank you for your thorough answer.
Thanks for this report, however I cannot reproduce this issue with described models. Can you provide a sample project?