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
28318	Django Doesn't Support Triple Joins Without Extra	ekarat	nobody	"This ticket is just to document a use case for QuerySet.extra as requested by the docs: ​https://docs.djangoproject.com/en/1.9/ref/models/querysets/#extra

Further reference and context on StackOverflow:  https://stackoverflow.com/questions/44577885/triple-join-in-django

I want to do a three way join between 3 tables.  For example, take the following QuerySet.extra WHERE

{{{WHERE = 't1.x=t2.x AND t1.y=t3.y AND t2.z=t3.z AND t2.value <= t3.value'}}}

or more concretely:

{{{A.objects.all().extra(select={'nextItem' : 't2.next', 'newValue' : 't3.value'}, tables=['""myapp_b"" AS ""t2""', '""myapp_c"" AS ""t3""'], where=['myapp_a.x_id=t3.x_id AND myapp_a.y_id=t2.y_id AND t2.z_id=t3.z_id AND t2.value <= t3.value'])}}}

I cannot find a way to replicate this without the extra or rawSQL methods, due to the 3-way connections between these models.

Of course, I can't quite use this approach either due to a couple of bugs with extra (which the documentation says is not being maintained):
* The table name cannot be referenced unless there is a select, which is why I have the dummy select for t3, even though I don't need it.
* If any of the columns have an uppercase character, Django converts it to lowercase, even though it is uppercase in the database, so the column name is not recognized.

The first has a workaround of selecting a field I don't strictly need.  The second requires changing the column names in the database, which is not feasible.

Reference for this second bug:
https://code.djangoproject.com/ticket/28317#ticket

Once again, the purpose of this ticket is to document a use case of QuerySet.extra that cannot be replicated via another method (or rawSQL).  I do think there would be a use to support three-way table joins of this nature.

If there is another Django feature that accomplishes this, please let me know!"	New feature	closed	Database layer (models, ORM)	1.10	Normal	duplicate	QuerySet.extra		Unreviewed	0	0	0	0	0	0