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()
)
query = ProductionModel.query
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.
