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
33999	Efficient filter with join on soft link.	brandic	nobody	"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'))
}}}

"	New feature	closed	Database layer (models, ORM)	3.2	Normal	wontfix	QuerySet.extra		Unreviewed	0	0	0	0	0	0