python之递归 CTE 查询

我在构建生成以下原始 SQL 查询所需的 SQLAlchemy 代码时遇到问题。

WITH RECURSIVE recruiters AS ( 
    SELECT  
        recruiter.id 
    FROM  
        recruiter 
    JOIN  
        recruiter_member ON recruiter.id = recruiter_member.recruiter_id 
    WHERE 
        recruiter_member.user_id = 'f12c617a-415c-4f8c-add0-81a597545be8' 
    UNION ALL 
    SELECT  
        children.id 
    FROM  
        recrutiers AS parents,  
        recruiter AS children 
    WHERE  
        children.recruiter_id = parents.id 
) 
SELECT 
    * 
FROM 
    recruiters 

这里的模型是Recruiter和RecruiterMember。我似乎无法正确使用 UNION。

请您参考如下方法:

没有更多细节，这是我能想到的最好的:

from sqlalchemy import orm 
 
parent = orm.aliased(Recruiter) 
child = orm.aliased(Recruiter) 
top_q = ( 
    orm.query.Query([Recruiter.id.label('id')]) 
    .join(RecruiterMember, Recruiter.id == RecruiterMember.recruiter_id) 
    .filter(RecruiterMember.user_id == 'f12c617a-415c-4f8c-add0-81a597545be8') 
    .cte(recursive=True)) 
bottom_q = ( 
    orm.query.Query([child.id.label('id')]) 
    .join(parent, parent.id == child.recruiter_id)) 
 
final_query = top_q.union_all(bottom_q) 
 
orm.query.Query([final_query.c.id]).with_session(session).all()