Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#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 Mariusz Felisiak, 4 years ago

Resolution: needsinfo
Status: newclosed

Thanks for this report, however I cannot reproduce this issue with described models. Can you provide a sample project?

comment:2 by Ammar-Shiekh, 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 Ammar-Shiekh, 4 years ago

Resolution: needsinfo
Status: closednew

I reopened this ticket since I've added more information about the bug in the previous comment

Thanks in advance

Last edited 4 years ago by Ammar-Shiekh (previous) (diff)

comment:4 by Mariusz Felisiak, 4 years ago

Resolution: needsinfo
Status: newclosed

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 Omar Altayyan, 4 years ago

Resolution: needsinfo
Status: closednew

@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?

in reply to:  4 comment:6 by Mariusz Felisiak, 4 years ago

Resolution: needsinfo
Status: newclosed

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 Omar Altayyan, 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.

Note: See TracTickets for help on using tickets.
Back to Top