CODEWITHSUNDEEP
pythonsqlalchemy
Rémi Desgrange
Rémi Desgrange

Reputation: 908

Association table in sqlalchemy core

I have 3 tables. Cable, PhysicalPoint and Interconnect.

Cable contain two PhysicalPoint (uphill, downhill) and Interconnect contain a PhysicalPoint. I'd like to request all Cable that have a PhysicalPoint in common with Interconnect. I figured it ou with SQL but not sqlalchemy.

#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))

I hope this is comprehensive enough... In advance thanks.

Edit: the sql query I wrote is like this :

SELECT c.* 
FROM cable c
  LEFT JOIN physicalpoint pdh ON (pdh.id = c.cab_downhill)
  LEFT JOIN physicalpoint puh ON (puh.id = c.cab_uphill)
  LEFT JOIN interconnect idh ON (idh.id = pdh.id)
  LEFT JOIN interconnect iuh ON (iuh.id = puh.id)
WHERE idh.id='XXXX' OR iuh.id='XXXX'

Upvotes: 1

Views: 337

Answers (1)

univerio
univerio

Reputation: 20518

You write it in the exact same way you wrote your SQL query:

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"))))

Upvotes: 1

Related Questions