flask中使用subquery完成中间表join连接

# 子查询
select table_A_id as tableA.table_A_id, table_B_pass as tableB.pass, max_table_A_id as max(tableA.id) from tableA 
left outer join tableC ON tableC.xx_id = tableA.table_A_id 
left outer join tableB ON tableB.id = tableC.passwd_id
left outer join tableB ON tableB.id = tableC.passwd_id
GROUP BY tableA.table_A_id)

#子查询结果关联
select x, c.table_A_id from tableA 
left outer join tableD on ...
left outer join (select * from ...)
GROUP BY 
where
order by xx ASC

sub_query = db.session.query(
    tableA.table_A_id.label('table_A_id'),
    tableB.pass.label('table_B_pass'),
    func.max(tableA.id).label('max_table_A_id')
).select_from(
    tableA
).outerjoin(
    tableC, tableC.xx_id == tableA.table_A_id
).outerjoin(
    tableB, tableB.id == tableC.passwd_id
).group_by(
    tableA.table_A_id
).subquery()

primary_query = db.session.query(
    tableD, sub_query.c.table_B_pass, tableA.status
).outerjoin(
    sub_query, sub_query.c.table_A_id == tableD.id
).outerjoin(
    tableA, tableA.id == sub_query.c.max_table_A_id
).filter(
    tableD.task_id == last_task.id
).order_by(tableD.id.asc())