Modeling a two-to-many relationship in an existing database

[madduck]

Hey folks, I am new to SQLAlchemy, but already loving it, and I am glad I chose to learn it for this project I am working on. I have to deal with an existing database, so I am replicating the schemata as models, and I can focus on reading only. The downside, obviously, is that I am stuck with what the database has to offer, and it's pretty badly designed, unfortunately.

One of the challenges I am facing is how to model an existing "two-to-many" relationship. There is no abstraction table, but instead one table has two columns that both point to another table. It's pretty similar to a child having a mother and a father ("two", I am purposely keeping it simple…), mother and father are both parents, and a parent has a number of children > 1 ("many").

Here is an example I cooked up to illustrate the point:

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()


class Parent(Base):
    __tablename__ = "parent_table"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children = relationship(
        "Child",
    )


class Child(Base):
    __tablename__ = "child_table"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    mother_id = Column(Integer, ForeignKey("parent_table.id"))
    mother = relationship("Parent", foreign_keys=mother_id)
    father_id = Column(Integer, ForeignKey("parent_table.id"))
    father = relationship("Parent", foreign_keys=father_id)


if __name__ == "__main__":
    mama = Parent(id=1, name="Mama")
    papa = Parent(id=2, name="Papa")

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

    print(daughter.mother.children)

This yields an error:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Parent.children - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

… and while I understand the problem, I think, I am totally blanking at how to approach this. I did search the Web, but I could not find anything that worked.

I would like to have relationships on both Parent (to their children), and Child to mother and father, separately, but I understand that Parent.children might just not be possible.

Have you got an idea on how to go about this?

Thank you,
m

PS: Related issues I found, but they didn't help me get further:

• Many-to-Many without table in the other end #12109
• Defining a many to many relationship from two separate relationships to the same model #11974

[madduck]

I got something working, using

class Parent(Base):
    # …
    children = relationship(
        "Child",
        primaryjoin="or_(Parent.id==Child.mother_id, Parent.id==Child.father_id)",
        viewonly=True,
    )

but this just returns an empty table. There is no back_populates after all, but even if I add back_populates="children" to the Child's mother & father relationships, that is not happening.

Is it even possible?

[dlax]

If we use a Session and flush it after adding ORM objects, it seems to work as far as I can tell:

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import DeclarativeBase, relationship


class Base(DeclarativeBase):
    pass


class Parent(Base):
    __tablename__ = "parent_table"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children = relationship(
        "Child",
        primaryjoin="or_(Parent.id == Child.father_id, Parent.id == Child.mother_id)",
        viewonly=True,
    )


class Child(Base):
    __tablename__ = "child_table"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    mother_id = Column(Integer, ForeignKey("parent_table.id"))
    mother = relationship("Parent", foreign_keys=mother_id)
    father_id = Column(Integer, ForeignKey("parent_table.id"))
    father = relationship("Parent", foreign_keys=father_id)


if __name__ == "__main__":
    from sqlalchemy import create_engine
    from sqlalchemy.orm import Session

    engine = create_engine("sqlite://")
    Base.metadata.create_all(engine)
    with Session(engine) as session:
        mama = Parent(id=1, name="Mama")
        papa = Parent(id=2, name="Papa")

        daughter = Child(id=1, name="Daughter", mother=mama, father=papa)
        son = Child(id=2, name="Son", mother=mama, father=papa)
        session.add_all([daughter, son])
        session.flush()

        print([c.name for c in mama.children])
        print([c.name for c in papa.children])

produces:

['Daughter', 'Son']
['Daughter', 'Son']

Probably due to viewonly=True in children relationship.

[madduck]

Oh of course, that makes sense. And it works also if I just read it all from the database. Thank you for helping me over this hump!

[madduck]

Here is the complete code that works for me, for future reference:

from sqlalchemy import Column, ForeignKey, Integer, String, create_engine, select
from sqlalchemy.orm import Session, aliased, declarative_base, relationship

Base = declarative_base()


class Parent(Base):
    __tablename__ = "parent_table"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    children = relationship(
        "Child",
        primaryjoin="or_(Parent.id==Child.mother_id, Parent.id==Child.father_id)",
        viewonly=True,
    )


class Child(Base):
    __tablename__ = "child_table"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    mother_id = Column(Integer, ForeignKey("parent_table.id"))
    mother = relationship(Parent, foreign_keys=mother_id, back_populates="children")
    father_id = Column(Integer, ForeignKey("parent_table.id"))
    father = relationship(Parent, foreign_keys=father_id, back_populates="children")


if __name__ == "__main__":
    mama = Parent(id=1, name="Mama")
    papa = Parent(id=2, name="Papa")

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

    engine = create_engine("sqlite://")
    Base.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 son in mama.children
        assert son in papa.children

        st = (
            select(Child, mum := aliased(Parent), dad := aliased(Parent))
            .join(mum, Child.mother_id == mum.id)
            .join(dad, Child.father_id == dad.id)
        )
        for child in session.execute(st):
            print(child)