Getting a triple of related id fields

[Dan Stromberg]

I've been banging on this for hours, but I seem to be getting nowhere.

I've tried more things that I can count, but here are two of my attempts:

# result = (
# Pipeline.query
# .select_from(Storage, NewProduct)
# .join(Storage, pipeline_alias1.storage_id == Storage.id)
# .join(NewProduct, Storage.product_id == NewProduct.id)
# .filter(pipeline_alias2.storage_id == storage_alias1.id)
# .filter(storage_alias2.product_id == product_alias1.id)
# )

result = (
Pipeline.query
.select_from(Pipeline, Storage, NewProduct)
.join(Storage, pipeline_alias1.storage_id == storage_alias1.id)
.join(NewProduct, storage_alias2.product_id == product_alias1.id)
)

I keep getting:
sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause
to join from, there are multiple FROMS which can join to this entity.
Please use the .select_from() method to establish an explicit left
side, as well as providing an explicit ON clause if not present
already to help resolve the ambiguity.

How can I tell SQLAlchemy which FROM to use?

Thanks!

[Mike Bayer]

step 1 is stop using that silly Flask extension that gives you "Pipeline.query", I can't tell what it is you want to SELECT from either by reading this query.

looks like you have pipeline_alias1 and pipeline_alias2, but you are selecting from plain Pipeline, nothing is joining to it however.

what do you actually want to SELECT from in the columns clause?

In any case, use select() with join_from(): https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-select-join that way there is no ambiguity.

> --
> 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/CAOvKW57frKP1EACaQ4zoRwu7kDAUULAbXx1xFi8eVdw69tZCZg%40mail.gmail.com.

[Dan Stromberg]

On Tue, Jun 20, 2023 at 3:47 PM Mike Bayer
<mike_not_...@zzzcomputing.com> wrote:
>
>
> step 1 is stop using that silly Flask extension that gives you "Pipeline.query", I can't tell what it is you want to SELECT from either by reading this query.

Wow, that made things a lot easier.

Is there any reason to avoid mixing Model.query with direct SQLAlchemy
calls? We have a bunch of Model.query right now. I'm wondering if
we'd need to rewrite all those upfront, or if we could redo them as
time permits.

Thanks!

[Mike Bayer]

they are perfectly fine to use, nothing is going away with any of that, so take any amount of time to migrate them (or not).

> --
> 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/CAOvKW55pTerGhFw6F7%3D%3DCgbz0fu2N71uUOgk73A91jP3M10AEQ%40mail.gmail.com.