hi -
same as the other message I just answered here, nobody can tell what would be
wrong with this query without a runnable example, please post **very succinct**
sample classes + the query you are seeking at
https://github.com/sqlalchemy/sqlalchemy/discussions
On Fri, Jun 23, 2023, at 6:10 AM, Piotr wrote:
> I have a problems with a CTE query using multiple consecutive joins (A -> B
> -> C -> D). A and B have both relation to other table so they are joined by
> that other_id value. C has FK to B and C-D are One to One (id=id).
>
> The query looks like so:
>
> latest_message = (session.query(
> models.Main.id.label('main_id'),
> message_models.Third.id,
> message_models.Third.type,
> message_models.Fourth.status,
> ).select_from(
> models.Main, models.Second, message_models.Third, message_models.Fourth,
> ).join(
> models.Second, models.Main.other_id == models.Second.other_id,
> ).join(
> message_models.Third, message_models.Third.second_id == models.Second.id,
> ).join(
> message_models.Fourth, message_models.Fourth.id ==
> message_models.Third.id,
> ).filter(
> models.Main.some_feld == 'abcd',
> ).distinct(
> models.Main.id,
> ).order_by(
> models.Main.id,
> message_models.Third.created_at.desc(),
> ).cte('latest_message'))
>
> Which results in an error:
>
> *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.*
>
> If I omit explicit join rules it manages to generate an SQL but it's not
> valid:
>
> *(psycopg2.errors.DuplicateAlias) table name "third_table" specified more
> than once*
> **
> And looking at it it does generate weird joins.
>
> Written by hand in SQL it works as needed - something like this:
>
> latest_message AS (
> SELECT DISTINCT ON (main_table.id) main_table.id as "main_id",
> third_table.id,
> third_table.type, fourth_table.status
> FROM main_table
> JOIN second_table ON second_table.other_id = main_table.other_id
> JOIN third_table ON
> third_table.second_id = second_table.id
> JOIN fourth_table ON fourth_table.id = third_table.id
> WHERE main_table.some_feld = 'abcd'
> ORDER BY main_table.id, third_table.created_at DESC
> )
>
> Anyone know how to fix the SQLAlchemy query version?
>
>
> --
> 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/db95cc7b-9a6e-4225-b58e-2566d1262d25n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/db95cc7b-9a6e-4225-b58e-2566d1262d25n%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/6a61b446-ec56-49b4-bd3e-deb6bbfa5061%40app.fastmail.com.
