Opened 3 months ago

Closed 3 months ago

#33730 closed Bug (invalid)

FilteredRelation join duplicated when referenced in multiple filter statements

Reported by: Ben Nace Owned by: nobody
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Given the following models

class Grandparent(models.Model):
    tag = models.CharField(max_length=100)
    enabled = models.BooleanField(default=True)

class Parent(models.Model):
    tag = models.CharField(max_length=100)
    grandparent = models.ForeignKey(Grandparent, on_delete=models.CASCADE)

class Child(models.Model):
    tag = models.CharField(max_length=100)
    parents = models.ManyToManyField(Parent)

When doing the following query with a filtered relation and a single filter statement, the SQL is generated as expected:

Child.objects.alias(a_parents=FilteredRelation('parents', condition=Q(tag='a'))).filter(a_parents__grandparent__enabled=True, a_parents__grandparent__tag='x')
SELECT "test_app_child"."id", "test_app_child"."tag" FROM "test_app_child" INNER JOIN "test_app_child_parents" ON ("test_app_child"."id" = "test_app_child_parents"."child_id") INNER JOIN "test_app_parent" a_parents ON ("test_app_child_parents"."parent_id" = a_parents."id" AND ("test_app_child"."tag" = 'a')) INNER JOIN "test_app_grandparent" ON (a_parents."grandparent_id" = "test_app_grandparent"."id") WHERE ("test_app_grandparent"."enabled" AND "test_app_grandparent"."tag" = 'x')

However, when the filter conditions are split across multiple calls to the filter function, say from them being applied in different functions, the joins for the filtered relation get duplicated in the SQL.

Child.objects.alias(a_parents=FilteredRelation('parents', condition=Q(tag='a'))).filter(a_parents__isnull=False).filter(a_parents__grandparent__tag='x')
SELECT "test_app_child"."id", "test_app_child"."tag" FROM "test_app_child" INNER JOIN "test_app_child_parents" ON ("test_app_child"."id" = "test_app_child_parents"."child_id") INNER JOIN "test_app_parent" a_parents ON ("test_app_child_parents"."parent_id" = a_parents."id" AND ("test_app_child"."tag" = 'a')) INNER JOIN "test_app_child_parents" T4 ON ("test_app_child"."id" = T4."child_id") INNER JOIN "test_app_parent" T5 ON (T4."parent_id" = T5."id" AND ("test_app_child"."tag" = 'a')) INNER JOIN "test_app_grandparent" ON (T5."grandparent_id" = "test_app_grandparent"."id") WHERE (a_parents."id" IS NOT NULL AND "test_app_grandparent"."tag" = 'x')

Change History (1)

comment:1 Changed 3 months ago by Mariusz Felisiak

Resolution: invalid
Status: newclosed

This is expected filter(A, B) and filter(A).filter(B) don't behave the same when multi-valued relationships are involved ​as documented.

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