Django Doesn't Support Triple Joins Without Extra

[ekarat]

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!

[Simon Charette]

Hello ekarat,

Support for conditional join is being worked on in #27332 and the issue you've reported in #28317 was caused by a misunderstanding of case handling by your database engine.

[ekarat]

Everything up through "I cannot find a way to replicate this without the extra or rawSQL methods, due to the 3-way connections between these models." is still valid.

I agree that this is similar to conditional_join, but conditional_join itself is not powerful enough to do this, unless you allow chained conditional joins involving F expressions over any of the tables involved.

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

is a tricky expression involving all possible pairwise relations between all 3 tables: A-B, B-C, and C-A, so you would need something like:

A.objects.conditional_join(B, x=F(A.x)).conditional_join(C,y=F(A.y),z=F(B.z),value__gte=F(B.value))

So, I think that whomever is working on conditional_join should be aware of this potential use case, and they can determine whether or not their implementation would be able to handle something like this.

[Simon Charette]

Looking up the ​actually proposed implementation it looks like it should be easily feasible to chain and use lookups to generate joins.

A.objects.filtered_relation(
    'b', alias='b2', condiiton=Q(b__x=F('x'))
).filtered_relation(
   'c', alias='c2', condition=Q(
        c__y=F('y'),
        c__z=F('b2__z'),
        c__value__gte=F('b2__value'),
   )
)

You might want to give the PR a review if you're interested in this feature.