Efficient filter with join on soft link.

[brandic]

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'))

[Mariusz Felisiak]

As far as I'm aware, using extra() is the right thing to do in your case. You could also try to change ModelB.c to a ForeignKey without a database constraint, e.g.

c = models.ForeignKey(ModelC, ..., db_constraint=False)

but it's a bad idea in most of cases (see ​docs).

Even if it's feasible I don't think there would be consensus to add that complexity to Django itself. You can raise the idea on the DevelopersMailingList to reach a wider audience and see what other think (see also ​the guidelines with regards to requesting features).