FilteredRelation is not working with GenericRelation

[Omar Altayyan]

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.

[Mariusz Felisiak]

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

[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.

[Mariusz Felisiak]

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

[Omar Altayyan]

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

[Mariusz Felisiak]

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.

[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.