How to make a left join in an update with sqlalchemy and mysql?

I am trying to craft a query with SQLAlchemy 2.5. My main goal is to do an update while :

doing a join on a "parent" table
and doing a left join between the "parent" table and a third table

I can't seem to make this work : I am always having either errors or Cartesian products.

I have tried a few options. Currently I am trying with a subquery but getting a Cartesian product :

from sqlalchemy import (
    DateTime,
    ForeignKey,
    Integer,
    create_engine,
    func,
    select,
    update,
)
from sqlalchemy.orm import DeclarativeBase, mapped_column, sessionmaker


###########
# MODELS  #
###########
class Base(DeclarativeBase):
    pass


class Parent(Base):
    __tablename__ = "parent"

    id = mapped_column(Integer, primary_key=True, nullable=False)
    updated_at = mapped_column(DateTime, nullable=True)


class Child(Base):
    __tablename__ = "child"

    id = mapped_column(Integer, primary_key=True, nullable=False)
    parent_id = mapped_column(Integer, ForeignKey("parent.id"))
    last_status_change = mapped_column(DateTime, nullable=True)


class OtherChild(Base):
    __tablename__ = "other_child"

    id = mapped_column(Integer, primary_key=True, nullable=False)
    parent_id = mapped_column(Integer, ForeignKey("parent.id"))


###########
# DB INIT #
###########

engine = create_engine("mysql://root:@127.0.0.1/dev?charset=utf8mb4")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

###########
# EXAMPLE #
###########

subq = (
    select(Parent.id, Parent.updated_at)
    .outerjoin(OtherChild)
    .where(OtherChild.id.is_(None))
).subquery()

stmt = (
    update(Child)
    .where(Child.parent_id.in_(select(subq.c.id)))
    .values(
        last_status_change=func.CONVERT_TZ(subq.c.updated_at, "Europe/Paris", "UTC")
    )
)

compiled = stmt.compile(dialect=engine.dialect, compile_kwargs={"literal_binds": True})
# print(compiled)
with Session() as session:
    session.execute(stmt)

In an nutshell, the SQL that I am trying to generate is like (I am aware of the possibility to use text() and raw SQL, but I'd like to make this work with the ORM) :

UPDATE
    child
JOIN parent ON
    parent.id = child.parent_id
LEFT JOIN other_child ON
    other_child.parent_id = parent.id
    SET
    last_status_change = CONVERT_TZ(parent.updated_at, 'Europe/Paris', 'UTC')
WHERE
    other_child.id IS NULL

What am I missing ?

Solution

You don't really need a subquery (though I am not sure if this can be considered a subquery).

from sqlalchemy import DateTime, ForeignKey, Integer, create_engine, func, join, update
from sqlalchemy.orm import DeclarativeBase, Session, mapped_column

class Base(DeclarativeBase):
    pass

class Parent(Base):
    __tablename__ = "parent"
    id = mapped_column(Integer, primary_key=True, nullable=False)
    updated_at = mapped_column(DateTime, nullable=True)

class Child(Base):
    __tablename__ = "child"
    id = mapped_column(Integer, primary_key=True, nullable=False)
    parent_id = mapped_column(Integer, ForeignKey("parent.id"))
    last_status_change = mapped_column(DateTime, nullable=True)

class OtherChild(Base):
    __tablename__ = "other_child"
    id = mapped_column(Integer, primary_key=True, nullable=False)
    parent_id = mapped_column(Integer, ForeignKey("parent.id"))


engine = create_engine("mysql+pymysql://", echo=True)
Base.metadata.create_all(engine)

with Session(engine) as session:
    joinq = join(
        join(Child, Parent, Parent.id == Child.parent_id),
        OtherChild,
        OtherChild.parent_id == Parent.id,
        isouter=True,
    )

    statement = (
        update(joinq)
        .values(
            {
                Child.last_status_change: func.CONVERT_TZ(
                    Parent.updated_at, "Europe/Paris", "UTC"
                )
            }
        )
        .where(OtherChild.id.is_(None))
    )

    session.execute(statement)

The following query is emitted by SQLAlchemy

UPDATE child
INNER JOIN parent ON parent.id = child.parent_id
LEFT OUTER JOIN other_child ON other_child.parent_id = parent.id
SET
    child.last_status_change = CONVERT_TZ(parent.updated_at, % (CONVERT_TZ_1) s, % (CONVERT_TZ_2) s)
WHERE
    other_child.id IS NULL