Secondary join in one-to-many relationship in SQLAlchemy
I have the following model (heavily simplified):
class Shipment(Base): id = Column(Integer, primary_key=True) account_id = Column(Integer, ForeignKey("accounts.id")) items = relationship("ShipmentItem") class ShipmentItem(Base): id = Column(Integer(), primary_key=True) shipment_id = Column(Integer, ForeignKey("shipments.id")) sku = Column(String) shipment = relationship(Shipment, uselist=False) product = relationship( "Product", primaryjoin="and_(Shipment.id == foreign(ShipmentItem.shipment_id)," "foreign(ShipmentItem.sku) == Product.sku," "foreign(Shipment.account_id) == Product.account_id)", uselist=False, ) class Product(Base): account_id = Column(Integer, ForeignKey("accounts.id"), primary_key=True) sku = Column(String, primary_key=True) Notice that the ShipmentItem.product relationship doesn’t have a direct reference to Shipment. Despite that, doing shipment_item.product works fine when I already have an instance at hand, but when trying to query Shipment.query().filter(Shipment.id == 1).options(defaultload(Shipment.shipment_items).joinedload(ShipmentItem.product)) it fails complaining that it doesn’t know the shipments.account_id column.
I’ve been trying to adapt these docs: https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#composite-secondary-joins to my case, but to no avail. The jonedload SQL I’d like to see emitted is something like:
SELECT * FROM shipment_items JOIN shipments ON shipments.id = shipment_items.shipment_id JOIN products ON products.account_id = shipments.account_id AND shipment_items.sku = products.sku WHERE shipment_items.shipment_id = 1 
