sqlalchemy: select from joined table

Viewed 47

How do i write this sql query in sqlalchemy?

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

I tried this:

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

Then I tried this:

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

I even tried this

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

But nothing works :(( I don't know how to write this sql query in sqlalchemy.. Monstrous thank you in advance!!

1 Answers

It happened to be, i had to use select_from() func

Related