I have 2 tables (defined as models in python): Productions and Tasks
A Production can contain 1 or more Tasks.
Tasks have a name (string), a priority value (int) and a state (TODO / ACTIVE / COMPLETE).
I need to find all productions with an ActiveTask of a specific name.

The ActiveTask of a production is defined as the first (lowest priority) task of the production which is in state TODO or ACTIVE.

The following mysql script does the trick:

SELECT production.*, active_task.*
    FROM PRODUCTIONS production
    JOIN LATERAL (
        SELECT active_task.*
        FROM PRODUCTIONTASKS active_task
        WHERE active_task.production_id = production.id
          AND active_task.state IN ('TODO', 'ACTIVE')
        ORDER BY active_task.priority
        LIMIT 1
    ) active_task ON TRUE

Now i need to implement this in my python application using sqlalchemy.

I have defined my Productions and Tasks using sqlalchemy.

class ProductionModel():
    __tablename__ = "PRODUCTIONS"

    product_id = Column(ForeignKey("PRODUCTS.id", name="product_id"), nullable=False, index=True)

class ProductionTaskModel():
    __tablename__ = "PRODUCTIONTASKS"

    production_id = Column(ForeignKey("PRODUCTIONS.id"), nullable=True, index=True)
    priority = Column(Integer())
    name = Column(String(45, collation="utf8mb4_bin"))
    state = Column(String(45, collation="utf8mb4_bin"))

I have tested various approaches, but none have worked.
This is an example:

ActiveTask = aliased(ProductionTaskModel)
    active_task_subquery = (
    select(ActiveTask)
    .where(
        ActiveTask.production_id == ProductionModel.id,
        ActiveTask.state.in_(['TODO', 'ACTIVE'])
    )
    .order_by(ActiveTask.priority)
    .limit(1)
    .lateral()
)

        productions = (
        query
        .select_from(ActiveTask)
        .join(active_task_subquery, true())
        .add_entity(ActiveTask)
        .limit(page_size)
        .all()
    )

But this gives the error:
sqlalchemy.exc.InvalidRequestError: Select statement ‘<sqlalchemy.sql.selectable.Select object at 0x7efcfc3b9590>’ returned no FROM clauses due to auto-correlation; specify correlate() to control correlation manually.