CODEWITHSUNDEEP
pythonormsqlalchemyforeign-keys
localusername
localusername

Reputation: 45

SQLAlchemy Multiple relationships between tables

Two declarative classes which have a parent child relationship, the youngest child is the most important child and thus a youngest_child_id column would be useful.

In this there are two relationships - a one to one from parent to child and a one to many from parent to children, but this creates multiple join paths

Something like the below:

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    youngest_child_id = Column(Integer, foreign_key='Child.id')
    youngest_child = relationship("Child", uselist=False, foreign_keys=[youngest_child_id])
    children = relationship("Child", back_populates='parent')

Class Child(Base):
    __tablename__ = 'children'
    id = id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, foreign_key='Parent.id')
    parent = relationship("Parent", back_populates='children')

This and a few other variations that I have created raise AmbiguousForeignKeysError:

Exception has occurred: sqlalchemy.exc.AmbiguousForeignKeysError
Could not determine join condition between parent/child tables on relationship Parent.children

Where is this going wrong and can this be achieved via the ORM?

Upvotes: 2

Views: 3060

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

You've defined foreign_keys for the youngest_child relationship, but you also have to define it for the children and parent relationships:

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    youngest_child_id = Column(Integer, ForeignKey('children.id'))
    youngest_child = relationship("Child", uselist=False, post_update=True,
                                  foreign_keys=[youngest_child_id])
    # Pass foreign_keys= as a Python executable string for lazy evaluation
    children = relationship("Child", back_populates='parent',
                            foreign_keys='[Child.parent_id]')

class Child(Base):
    __tablename__ = 'children'
    id = id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parents.id'))
    parent = relationship("Parent", back_populates='children',
                          foreign_keys=[parent_id])

In addition you must define post_update=True on for example youngest_child in order to break the circular dependency between the models. Without it SQLAlchemy would have to insert both the parent and the child at the same time, if you do something like this:

p = Parent()
c1, c2 = Child(), Child()
p.children = [c1, c2]
p.youngest_child = c1
session.add(p)
session.commit()

With the post update in place SQLAlchemy first inserts to parents, then to children, and then updates the parent with the youngest child.

Upvotes: 4

Related Questions