[Django] #32239: FilteredRelation is not working with GenericRelation
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
omaraltayyan |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) | Keywords: FilteredRelation
Severity: Normal | GenericRelation Generic Relation
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32239>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: FilteredRelation | Triage Stage:
GenericRelation Generic Relation | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
Thanks for this report, however I cannot reproduce this issue with
described models. Can you provide a sample project?
--
Ticket URL: <https://code.djangoproject.com/ticket/32239#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: FilteredRelation | Triage Stage:
GenericRelation Generic Relation | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Ammar-Shiekh):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32239#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
GenericRelation Generic Relation | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: needsinfo =>
--
Ticket URL: <https://code.djangoproject.com/ticket/32239#comment:3>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: FilteredRelation | Triage Stage:
GenericRelation Generic Relation | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32239#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
GenericRelation Generic Relation | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: needsinfo =>
Comment:
@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?
--
Ticket URL: <https://code.djangoproject.com/ticket/32239#comment:5>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: FilteredRelation | Triage Stage:
GenericRelation Generic Relation | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32239#comment:6>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: FilteredRelation | Triage Stage:
GenericRelation Generic Relation | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Omar Altayyan):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32239#comment:7>