Left join and nested inner join

[Michael Ekoka]

Hi, I'm looking for the SQLAlchemy equivalent to the query

SELECT *
FROM a
LEFT OUTER JOIN (b INNER JOIN c ON b.id = c.b_id)
ON a.id = b.a_id

Related:

https://stackoverflow.com/a/56815807/56974

https://stackoverflow.com/questions/25514160/nested-joins-in-sqlalchemy

Table "b" and "c" are joined and filtered first, then the outer join is applied. I was able to achieve the same results using a subquery, whose fields I was subsequently able to load using `contains_eager`. FYI

subq = session.query(B).join(C).subquery(with_labels=True)
q = (session.query(A)
     .outerjoin(subq, A.id==subq.c.b_a_id)
     .options(contains_eager(A.b, alias=subq)
              .options(contains_eager(B.c, alias=subq))))
r = q.all()

I'm curious whether there's an equivalent using the above nested join syntax.

Thanks.

[Mike Bayer]

use the join construct directly

from sqlalchemy.orm import join

stmt = select(A).outerjoin(join(B, C), A.id == B.a_id)

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/86b6cc02-be68-400f-9a13-ef486b560329n%40googlegroups.com.

[Michael Ekoka]

Thanks, it worked.