Query object having multiple entities fails when trying to join

[Andrey Semenov]

Session.query(Address, Order, OrderLog).outerjoin(OrderLog, Order.id == OrderLog.order_id)

This code produces a query like:

SELECT ...
FROM orders, addresses LEFT OUTER JOIN order_logs ON orders.id = order_logs.order_id

This leads to an error at server side:

ProgrammingError: (psycopg2.ProgrammingError) invalid reference to FROM-clause entry for table "orders"
LINE 3: ...M orders, addresses LEFT OUTER JOIN order_logs ON orders.id ...
                                                             ^

so, the autowiring joins (auto-grouping them by entity relationship given at join_clauses) does not work. The docs don't say how to explicitly point the left part of join.

The query I'm trying to build up is like:

SELECT ...

FROM entity_1 JOIN entity_1_1 ON ..., entity_2 JOIN entity_2_1 ON ..., entity_3, entity_4 JOIN entity_4_1 ON ...

Current query builder chains all .join() of Query objects to the first entity of the _entities list

[Mike Bayer]

• add an additional note regarding select_from, fixes #3546

→ <<cset d84dea62de3a>>

[Mike Bayer]

• add an additional note regarding select_from, fixes #3546

(cherry picked from commit d84dea62de3a9c83ad539a5cf2ff4be2c0685a94)

→ <<cset 6da108ce6cdb>>

[Mike Bayer]

• add an additional note regarding select_from, fixes #3546

(cherry picked from commit d84dea62de3a9c83ad539a5cf2ff4be2c0685a94)

→ <<cset 276df23935fe>>

[Mike Bayer]

I've added additional notes to the ORM tutorial to ensure people don't miss this, but please see "Controlling what to Join From" at http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.join and http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.select_from - thanks!

[Andrey Semenov]

Well, the change still confuses at the point that select_from explicitly sets the 'FROM' clause entries list. Could you please provide a working example on how to query the result like: .query(Bucket, Order, Address, OrderLog, Region).join(OrderLog, Order.id == OrderLog.order_id).join(Region, Address.region_id == Region.id).filter(Order.id == any(Bucket.order_ids))? Do I need to call select_from twice?

[Mike Bayer]

that seems like you are looking for a query of the form:

SELECT * FROM  order JOIN orderlog ON <onclause>, region JOIN address ON <onclause>

e.g. comma-separated FROM entries where each is a join. That's an extremely unusual request as the vast majority of cases just want a single-chained JOIN. Calling select_from() twice may work, select_from() also accepts multiple elements so you might just want to pass a list of join() objects to it, e.g. select_from(join(Order, OrderLog), join(Address, Region)).

[Andrey Semenov]

Ok. I'll give it a try. Thank you.