sqlalchemy:从联接表中选择

Question

如何用sqlalchemy编写这个sql查询？

SELECT
    n.name
FROM
    project_nomenclatures pn
    JOIN nomenclatures_sections ns on pn.nomenclature_id = ns.nomenclature_parent_id
    JOIN nomenclatures n on ns.nomenclature_id = n.id
WHERE
    pn.id = 2018

我试过这个：

        sql = (
            select(Nomenclature)
            .join(NomenclatureSection, and_(
                NomenclatureSection.nomenclature_id == Nomenclature.id,
                NomenclatureSection.nomenclature_parent_id == ProjectNomenclature.nomenclature_id
            ))
            .join(ProjectNomenclature, ProjectNomenclature.id == 2018)
        )

然后我试了一下：

        sql = (
            select(ProjectNomenclature, Nomenclature)
            .join(NomenclatureSection, NomenclatureSection.nomenclature_parent_id == ProjectNomenclature.nomenclature_id)
            .join(Nomenclature, NomenclatureSection.nomenclature_id == Nomenclature.id)
            .where(ProjectNomenclature.id == 2018)
        )

我甚至试过这个

        sql = (
            select(Nomenclature)
            .join(NomenclatureSection, NomenclatureSection.nomenclature_parent_id == ProjectNomenclature.nomenclature_id)
            .join(ProjectNomenclature, NomenclatureSection.id == ProjectNomenclature.nomenclature_id)
            .where(ProjectNomenclature.id == 2018)
        )

(我不知道如何用sqlalchemy编写这个sql查询。)提前谢谢你！！

Answer 1

碰巧，我不得不使用select_from() func