SQLAlchemy query shows error "Can't join table/selectable 'workflows' to itself"
Part
.query(Workflow.user_id, func.count(Log.id))
adds both Workflow
and Log
to your query. The first model is marked as primary table and others are marked as secondary. If there is no calls to .join()
afterwards, both primary and secondary tables will be added to the FROM
clause. If there are calls to .join()
it will move table it receives to the JOIN
clause. The important thing here is that .join()
can be applied only to the secondary table.
The problem is that your call to
.join(Workflow, Workflow.id == Log.workflow_id)
tries to mark primary table as joined. To fix issue you need to join secondary table:
.join(Log, Workflow.id == Log.workflow_id)
You can add echo=True
to see SQL generated by SQLAlchemy. It's really convenient to debug your queries. Or you can compile single query to see generated SQL.