'SQLAlchemy appends aliased Table in FROM clause
I'm creating an application with a flask backend and I'm using SQLAlchemy Core to create the SQL-Queries. Now I'm creating a base select like this:
SYSTEM = self.__TABLES.SYSTEM
RESPONSIBLE = self.__TABLES.responsible
INFRA_RESPONSIBLE = RESPONSIBLE.alias('INFRA_RES')
SYSTEM_RESPONSIBLE = RESPONSIBLE.alias('SYS_RES')
STMT = SYSTEM \
.outerjoin(INFRA_RESPONSIBLE, INFRA_RESPONSIBLE.c.id == SYSTEM.c.infrares) \
.outerjoin(SYSTEM_RESPONSIBLE, SYSTEM_RESPONSIBLE.c.id == SYSTEM.c.sysres)
Now, if I'm creating a SQL-Query like this:
STMT = select([SYSTEM]) \
.select_from(STMT) \
.where(INFRA_RESPONSIBLE.c.id == 1)
This is how the generated looks like:
SELECT "it".systems.*
FROM "it".responsible AS "INFRA_RES", "it".systems
LEFT OUTER JOIN "it".responsible as "INFRA_RES" ON "INFRA_RES".id = "it".systems.infrares
LEFT OUTER JOIN "it".responsible as "SYS_RES" ON "SYS_RES".id = "it".systems.sysres
WHERE "INFRA_RES".id = 1
But i want to look it like this:
SELECT "it".systems.*
"it".systems
LEFT OUTER JOIN "it".responsible as "INFRA_RES" ON "INFRA_RES".id = "it".systems.infrares
LEFT OUTER JOIN "it".responsible as "SYS_RES" ON "SYS_RES".id = "it".systems.sysres
WHERE "INFRA_RES".id = 1
The table INFRA_RES is already joined with the base statement, how can I achieve to build a proper SQL-Statement?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
