Two-to-many relationship

[madduck]

I spent a bit more time than I wanted on getting a two-to-many relationship working, and so I thought I'd better document it here for others to benefit:

from sqlalchemy.orm import aliased
from sqlmodel import Field, Relationship, SQLModel, Session, create_engine, select


class Parent(SQLModel, table=True):
    id: int | None = Field(primary_key=True, default=None)
    name: str
    children: list["Child"] = Relationship(
        sa_relationship_kwargs={
            "primaryjoin": "or_(Parent.id==Child.mother_id, Parent.id==Child.father_id)",
            "viewonly": True,
        }
    )


class Child(SQLModel, table=True):
    id: int | None = Field(primary_key=True, default=None)
    name: str
    mother_id: int | None = Field(foreign_key="parent.id", default=None)
    mother: Parent = Relationship(
        back_populates="children",
        sa_relationship_kwargs={"foreign_keys": "Child.mother_id"},
    )
    father_id: int | None = Field(foreign_key="parent.id", default=None)
    father: Parent = Relationship(
        back_populates="children",
        sa_relationship_kwargs={"foreign_keys": "Child.father_id"},
    )


if __name__ == "__main__":
    mama = Parent(name="Mama")
    papa = Parent(name="Papa")

    daughter = Child(name="Daughter", mother=mama, father=papa)
    son = Child(name="Son", mother=mama, father=papa)

    engine = create_engine("sqlite://")
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        session.add_all([mama, papa, daughter, son])
        session.commit()

        assert daughter.mother == mama
        assert daughter.father == papa
        assert son.mother == mama
        assert son.father == papa

        assert daughter in mama.children
        assert daughter in papa.children
        assert son in mama.children
        assert son in papa.children

        print(daughter.mother.name)
        print(daughter.father.name)
        print(son.mother.name)
        print(son.father.name)
        print(mama.children)
        print(papa.children)

        st = (
            select(Child, mum := aliased(Parent), dad := aliased(Parent))
            .join(mum, col(Child.mother_id) == mum.id)  # pyright reports an error here,
            .join(dad, col(Child.father_id) == dad.id)  # and here… see below
        )
        for child in session.exec(st):
            print(child)

Thanks to @dlax and also and CaselIT (Federico Caselli) for their help!

Update 2025-08-22: Thanks to the suggestion by @YuriiMotov, the previous pyright error on the two join lines towards the bottom as now been resolved through the use of col.