id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
33730	FilteredRelation join duplicated when referenced in multiple filter statements	Ben Nace	nobody	"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')
}}}




"	Bug	closed	Database layer (models, ORM)	3.2	Normal	invalid			Unreviewed	0	0	0	0	0	0