use the join construct directly from sqlalchemy.orm import join
stmt = select(A).outerjoin(join(B, C), A.id == B.a_id) On Mon, Jul 3, 2023, at 8:29 AM, Michael Ekoka wrote: > > 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. > > > > > -- > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/86b6cc02-be68-400f-9a13-ef486b560329n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/86b6cc02-be68-400f-9a13-ef486b560329n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/281524be-e25c-4e37-ad18-397a0908d53b%40app.fastmail.com.
