sqlalchemy 遞迴查詢

干炸小黄鱼發表於2024-07-15
SQL遞迴

背景: 有個一個組織機構. 並沒有設計父級id, 只有id, title, nature 三個欄位, nature=1/2/3/4 分別表示級幾級單位;
現在要模糊查詢本級, 上級, 上上級, 上上上級的title中包含 特定字元的記錄;使用sqlalchemy遞迴查詢,下面程式碼沒除錯,不保證執行, 主要看思路;

def find_orgs_with_aa(session: Session):
    # 使用遞迴CTE查詢，首先定義CTE的基本查詢和遞迴部分
    cte_query = (
        session.query(
            Organization.id,
            Organization.title,
            Organization.nature,
            func.cast(None, Integer).label('parent_id')  # 初始化沒有父級ID
        )
        .filter(Organization.title.contains('aa'), Organization.nature == 1)  # 找到所有一級機構包含aa的
    
        .union_all(
            # 遞迴部分，查詢所有上級機構（nature較小的）
            session.query(
                Organization.id,
                Organization.title,
                Organization.nature,
                Organization.id.label('parent_id')  # 使用當前機構的ID作為父級ID
            )
            .join(Organization, Organization.id == Organization.parent_id)  # 自連線，找到上級
            .filter(Organization.title.contains('aa'))  # 上級機構標題也需包含aa
        )
    ).cte(name='recursive_cte', recursive=True)

    # 最終查詢，從CTE中選擇所有需要的記錄
    final_query = session.query(Organization).select_from(cte_query).subquery()
    result = session.query(final_query).all()

    return result

相關文章