Today I learned: Primary and Secondary Joins

While developing my app I had to shape the data I get from the SQLAlchemy in a simplified way. Given models Client, Order, Receipt, where Order is referencing Client and Receipt is referencing Order I wanted to access in a view only relationship all receipts directly from the Client model. The way this is done is via primary and secondary joins.

class Client(Base):
    __tablename__ = "clients"

    id = Column(Integer, primary_key=True)
    name = Column(String)
    entity = Column(String)

    receipts = relationship("Receipt", secondary="orders", primaryjoin="Client.id==Order.client_id",secondaryjoin="Receipt.order_uuid==Order.uuid", viewonly=True)


class Order(Base):
    __tablename__ = "orders"

    uuid = Column(UUID(as_uuid=True), primary_key=True)
    client_id = Column(Integer, ForeignKey("clients.id", ondelete="CASCADE"))
    created = Column(DateTime)


class Receipt(Base):
    __tablename__ = "receipts"

    uuid = Column(UUID(as_uuid=True), primary_key=True)
    order_uuid = Column(UUID(as_uuid=True), ForeignKey("orders.uuid", ondelete="CASCADE"))
    created = Column(DateTime)

Of course such a relationship can only be read-only because in this case it would be impossible to preserve the references to allow an editable relationship.

So far, so good. Good luck!!!