#29271: Chaining Filters on a Reverse Foreign Key Produces Multiple Joins
-------------------------------------+-------------------------------------
Reporter: Michael | Owner: nobody
MacIntosh |
Type: Bug | Status: new
Component: Database | Version: 2.0
layer (models, ORM) | Keywords: filter chain
Severity: Normal | reverse foreign key
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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 )
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29271>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/056.ea1c355151c84e9aff94309bd61a0eef%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
