[Django] #33999: Efficient filter with join on soft link

[Django]

#33999: Efficient filter with join on soft link
-------------------------------------+-------------------------------------
Reporter: brandic | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: | Version: 3.2
Uncategorized |
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
We recently had cause to use "extra" in our codebase. We needed to filter
on a table which was linked to another table via an integer (a soft link,
for business needs) on an intermediary table. (i.e. Model A has a
ForeignKey to Model B which is linked to Model C via an IntegerField; we
needed to filter on Model A by a field on Model C.)

Using the solution with "annotate" produced a query with two subqueries
(three total queries); using extra we achieved an equivalent query using
joins (one total query.)

----

SQL from query with "annotate":

{{{
SELECT [all fields from model_a],
(SELECT model_c.target_field FROM model_c JOIN model_b ON model_c.id =
model_b.c_id WHERE model_b.a_id = model_a.id) AS "target_field"

FROM "model_a"

WHERE (SELECT model_c.target_field FROM model_b JOIN model_c ON model_c.id
= model_b.c_id WHERE model_b.a_id = model_a.id)::text = 'some string')
}}}

----

SQL from query with "extra":

{{{
SELECT [all fields from model_a]

FROM "model_a" , "model_b" , "model_c"

WHERE (model_c.id = model_b.c_id AND model_b.a_id = model_a.id AND
model_c.target_field = 'some string'))
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33999>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.