Problems with multiple consecutive joins

[Piotr]

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?

[Mike Bayer]

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

--

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/db95cc7b-9a6e-4225-b58e-2566d1262d25n%40googlegroups.com.