I have four classes, Category, Product, Attribute, and AttributeValue such that

class Category(Base):
    ...
    id: int

class Product(Base):
    ...
    id: int
    categoryId: int # Foreign key on Category

class Attribute(Base):
    ...
    id: int
    categoryId: int # Foreign key on Category

class AttributeValue(Base):
    ...
    id: int
    attributeId: int # Foreign key on Attribute
    productId: int # Foreign key on Product

I want to be able to configure the object model in such a way that a product has attributes, and attributes have values for the product, such that product.attribute.value returns the specific attribute value for that product.

I can join Attribute to Product on the shared id categoryId, such that

class Product(Base):
    ...
    attribute: Mapped[Attribute] = relationship(
        "Attribute",
        primaryjoin=categoryId == Attribute.categoryId
    )

but within attribute, if I try to join on AttributeValue

class Attribute(Base):
    ...
    value: Mapped[AttributeValue] = relationship(
        "AttributeValue",
        primaryjoin=and_(
            id==AttributeValue.attributeId,
            AttributeValue.productId == ??? # No key to join on here
    )

I’ve also tried hybrid_property and column_property within SQLAlchemy to expose productId within the Attribute class itself, however, joining on this results in an execution error where the generated SQL tries to insert a function as a parameter rather than an int for the id.

In SQL, it would be simple as

select *
from product p
join attribute a on a.categoryId = a.categoryId
join attributeValue av on av.attributeId = a.id and av.productId = p.id

Is there a recommended approach for this relationship?