pythonforeign-keyssqlalchemyclause

How to map multiple foreign keys to the same parent in SQLAlchemy?


Upon adding a second foreign key to the same table I get the following error:

Please specify the 'onclause' of this join explicitly.

How can I specify this relationship?

class Parent(Base):
    First = Column(Integer, ForeignKey('Child.Ex1'))
    Second = Column(Integer, ForeignKey('Child.Ex2'))

class Child(Base):
    Ex1 = Column(Integer)
    Ex2 = Column(Integer)

Solution

  • (ed. note: pep8 recommends naming class attributes starting with lowercase...just a convention)

    class Parent(Base):
        __tablename__ = "Parent"
        id = Column(Integer, primary_key=True)
        first = Column("First", Integer, ForeignKey('Child.Ex1'))
        second = Column("Second", Integer, ForeignKey('Child.Ex2'))
    
        first_child = relationship("Child", primaryjoin="Parent.first==Child.ex1")
        second_child = relationship("Child", primaryjoin="Parent.second==Child.ex2")
    
    class Child(Base):
        __tablename__ = "Child"
        id = Column(Integer, primary_key=True)
        ex1 = Column("Ex1", Integer)
        ex2 = Column("Ex2", Integer)