I had to create a relationship that needed to combine five different tables on a project I was working on. The SQLAlchemy documentation about composite secondary joins has an example for combining four tables, and in this blog post, I am extending it to five.

We start with a Company model. That model represents, well, a company. That company will have users, which we represent with the User model with a foreign key pointing to their company.

In addition to that, we have Product model that represents some sort of service that one or more companies might use. We model this with a Product model and a ProductSubscription model with foreign keys to a product and company. Each user can configure how they use a product their company is subscribed to. This is represented with a ProductUserConfiguration model that contains foreign keys to the user and to the product.

Putting this all together, the models look like this:

class Company(Base):
    __tablename__ = "company"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64), nullable=False)


class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64), nullable=False)
    company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))


class Product(Base):
    __tablename__ = "product"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64), nullable=False)


class ProductSubscription(Base):
    __tablename__ = "product_subscription"

    id: Mapped[int] = mapped_column(primary_key=True)
    company_id: Mapped[int] = mapped_column(ForeignKey("company.id"))
    product_id: Mapped[int] = mapped_column(ForeignKey("product.id"))


class ProductConfiguration(Base):
    __tablename__ = "product_configuration"

    id: Mapped[int] = mapped_column(primary_key=True)
    some_setting: Mapped[str] = mapped_column(String(64), nullable=False)
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
    product_id: Mapped[int] = mapped_column(ForeignKey("product.id"))

Now, I want a relationship on ProductConfiguration that returns the ProductSubscription related to that configuration. In other words, it has to return the ProductSubscription with a ProductSubscription.product_id matching ProductConfiguration.product_id, and with a ProductSubscription.customer_id matching the User.company_id of the User with User.id matching the ProductConfiguration.user_id. Easy-peasy, right?

Well, this is what it looks like as a SQLAlchemy relationship:

class ProductConfiguration(Base):
    __tablename__ = "product_configuration"

    id: Mapped[int] = mapped_column(primary_key=True)
    some_setting: Mapped[str] = mapped_column(String(64), nullable=False)
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
    product_id: Mapped[int] = mapped_column(ForeignKey("product.id"))

    subscription = relationship(
        ProductSubscription,
        secondary="join(User, ProductSubscription, User.company_id == ProductSubscription.company_id).join(Product, Product.id == ProductSubscription.product_id)",
        primaryjoin="and_(ProductConfiguration.product_id == Product.id, ProductConfiguration.user_id == User.id)",
        secondaryjoin="and_(ProductSubscription.company_id == User.company_id, ProductSubscription.product_id  == Product.id)",
        uselist=False,
        viewonly=True,
    )

If you get confused with this example, try to understand the example from the SQLAlchemy documentation first, as it combines only four tables instead of five, and it's a bit easier to follow.

It's also interesting to read about secondary, primaryjoin, and secondaryjoin.

Fortunately, I don't need to forge this type of relationships often!

Happy coding!