#33730: FilteredRelation join duplicated when referenced in multiple filter
statements
-------------------------------------+-------------------------------------
Reporter: Ben Nace | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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')
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33730>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070180e2b14936-3bdcb404-7bd6-46e7-b6c9-773f6beb757c-000000%40eu-central-1.amazonses.com.
