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
29271	Chaining Filters on a Reverse Foreign Key Produces Multiple Joins	Michael MacIntosh	nobody	"When you perform multiple filters on a queryset on a reverse foreign key, it produces duplicate joins, which causes the results to multiply.  If instead of chaining the filters, you put both of the filters in one filter, you do not get duplicate results.

You also get this behavior if you repeat the same filter.

This also applies to Q objects that are used in chain filters that reference values across reverse foreign keys as well.

Example Models:

{{{
class Alpha(models.Model):
    field = models.CharField(max_length=100)

class Beta(models.Model):
    field = models.CharField(max_length=100)
    data = models.CharField(max_length=100)
    alpha = models.ForeignKey(Alpha, on_delete=models.CASCADE)
}}}
Example Data:

{{{
Alpha.objects.all().delete()
Beta.objects.all().delete()
alpha = Alpha(field=""alpha_text"")
alpha.save()
beta = Beta(field=""beta_text"", data=""data1"", alpha=alpha)
beta.save()
beta = Beta(field=""beta_text"", data=""data2"", alpha=alpha)
beta.save()
}}}

Example Output:
{{{
>>> Alpha.objects.filter(beta__field=""beta_text"", beta__data=""data1"")
<QuerySet [<Alpha: Alpha object (12)>]>
>>> Alpha.objects.filter(beta__field=""beta_text"").filter(beta__data=""data1"")
<QuerySet [<Alpha: Alpha object (12)>, <Alpha: Alpha object (12)>]>
}}}

Formatted SQL of the first query (expected):

{{{
SELECT ""test_app_alpha"".""id"",
       ""test_app_alpha"".""field""
FROM   ""test_app_alpha""
       INNER JOIN ""test_app_beta""
               ON ( ""test_app_alpha"".""id"" = ""test_app_beta"".""alpha_id"" )
WHERE  ( ""test_app_beta"".""field"" = beta_text
         AND ""test_app_beta"".""data"" = data1 )  
}}}
Formatted SQL of the second query (bug)

{{{
SELECT ""test_app_alpha"".""id"",
       ""test_app_alpha"".""field""
FROM   ""test_app_alpha""
       INNER JOIN ""test_app_beta""
               ON ( ""test_app_alpha"".""id"" = ""test_app_beta"".""alpha_id"" )
       INNER JOIN ""test_app_beta"" T3
               ON ( ""test_app_alpha"".""id"" = T3.""alpha_id"" )
WHERE  ( ""test_app_beta"".""field"" = beta_text
         AND T3.""data"" = data1 )  
}}}

"	Bug	closed	Database layer (models, ORM)	2.0	Normal	invalid	filter chain reverse foreign key		Unreviewed	0	0	0	0	0	0