sqlalchemy中的关联表

Question

我有三张桌子。Cable、PhysicalPoint和{}。在

Cable包含两个PhysicalPoint（上坡、下坡）和{}包含一个物理点。我想请求所有与Interconnect有相同PhysicalPoint的Cable。我想是用SQL而不是SQL炼金术。在

#cable, physicalpoint and interconnect are sqlalchemy.Table
c = cable.alias('c')
pdh = physicalpoint.alias('pdh')
puh = physicalpoint.alias('puh')
idh = interconnect.alias('idh')
iuh = interconnect.alias('iuh')

j = c.outerjoin(pdh, pdh.c.id==c.c.cab_downhill)\
    .outerjoin(puh, puh.c.id==c.c.cab_uphill)

# this is where I don't know what to do...
j2 = outerjoin(idh, pdh, pdh.c.id==idh.c.id)
j3 = outerjoin(iuh, puh, puh.c.id==iuh.c.id)

# this works but how to integrate the two other join ?
db.execute(select(c.c).select_from(j))

我希望这足够全面。。。提前谢谢。在

编辑： 我写的sql查询是这样的：

^{pr2}$

Answer 1

您的编写方式与编写SQL查询的方式完全相同：

j = c.outerjoin(pdh, pdh.c.id==c.c.cab_downhill)\
    .outerjoin(puh, puh.c.id==c.c.cab_uphill)\
    .outerjoin(idh, idh.c.id==pdh.c.id)\
    .outerjoin(iuh, iuh.c.id==puh.c.id)
session.execute(select([c]).select_from(j).where(or_(idh.c.id == "foo", iuh.c.id == "foo"))))