#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.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/010701831ebb4888-53ec5cda-01b3-4f25-a1c0-ff32e19e9f18-000000%40eu-central-1.amazonses.com.
