Re: [sqlalchemy] Left join and nested inner join

[Michael Ekoka]

Thanks, it worked.

On Monday, July 3, 2023 at 8:49:58 PM UTC+7 Mike Bayer wrote:

> 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+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/86b6cc02-be68-400f-9a13-ef486b560329n%40googlegroups.com
>  
> 
> .
>
>
>

-- 
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/88a837ce-3013-4f33-bdee-fd90d93d9ca6n%40googlegroups.com.

Re: [sqlalchemy] Left join and nested inner join

[Mike Bayer]

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
>  
> .

-- 
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.

[sqlalchemy] 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.


-- 
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.