query.join fails silently with an intermediary type class

[Former user]

alias1 = aliased(my_class) alias2 = aliased(my_class) query.join(alias1.relation1) query.join(alias2.relation2)

where relation1 and relation2 are two different relations pointing to different attributes of a intermediary class.

whichever order the joins are applied in, the latter always fails. the second table is included in the query's from clause but no JOIN clause is provided.

[Mike Bayer]

The form of join() you are most likely using is one I considered removing for its confusing nature in #1445.

join() has the effect of not rendering the "left" or "right" side of a join twice. this allows you to say:

query(A).join(A.bs, B.cs)join(A.bs, B.ds)

where the join from A->B is only rendered once, forming the basis for the join of B->C and B->D.

In this case, if I remove this check, you get this SQL:

select * from alias1 JOIN intermediary ON <>, alias2 JOIN intermediary ON <>

which isn't really correct. you really mean to have "intermediary" on the left side.

The proper form of query.join() with an alias is described at http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-aliases , and you should be using that.

I don't really have a solution for the "backwards" feature since in this case it would require that join() figure out that both joins need to be "flipped around", placed around the common "source" of "intermediary", except to reconsider disabling it entirely, which would be unfortunate since it's supremely handy. Or perhaps just allowing it to render the above SQL which would then fail on the backend. A patch is attached for that behavior.

[Mike Bayer]

looking at #1445 presents the possibility that we would reject the "backwards" join if the non-tuple form of join were used, i.e. the one in your example. You'd need to say:

query(Intermediary).join((alias1, alias1.relation1)).join((alias2, alias2.relation2))

[Mike Bayer]

...except that is already the format we've made illegal here. This would require a major overhaul of the "backwards" feature to be better integrated.

[Mike Bayer]

im leaning towards the "incorrect" SQL at the moment, since it most directly represents what's asked for. there is a branch where I'm rebuilding Query.join() from scratch in branches/query_join_refactor that does this. this is all dependent on the branch moving forward, and its all 0.6 stuff.

[Mike Bayer]

the branch is in and that's committed to trunk in 2dfc500ac3adc68f6bb6f38c821705661cb6ecc2. So your join() here will still produce invalid SQL but it won't be silent.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)