Using SQLAlchemy with multiple self-referential foreign keys

foreign-keys, python, relational-database, sqlalchemy

Solution

The code below shows how to configure the relationship:

class NodeClass(Base):
    __tablename__ = 'nodes_table'
    node_id = Column(Integer, primary_key=True)

    left_id = Column(Integer, ForeignKey('nodes_table.node_id'))
    left = relationship('NodeClass', remote_side=[node_id],
            primaryjoin=('NodeClass.left_id==NodeClass.node_id'),
            backref=backref("right", uselist=False),
            #lazy="joined", join_depth=9,
            )

But few things should be noted:

- only one end of the relationship is stored, the other is inferred. This might not be what you want, but it is much more simple to manage, and it is enough to set one side only `myNode.left = myOtherNode` and the other (`right`) will be set automatically (because of configured `backref`)

- if both ends are stored (`right` and `left`), then

- both ends would need to be set in code and one has to ensure they are consistent, which might not be a trivial task

- the insert of two nodes that are linked would require `insert-1, insert-2, update-1` in case your primary key is computed on the database, as it is unknown during the first `insert`.

UPDATE: Sample code to the UPDATE part of the question (but still using the original class name). One needs only to specify the `primaryjoin` and `uselist=False`:

class NodeClass(Base):
    __tablename__ = 'nodes_table'
    node_id = Column(Integer, primary_key=True)

    left_id = Column(Integer, ForeignKey('nodes_table.node_id'))
    right_id = Column(Integer, ForeignKey('nodes_table.node_id'))

    left = relationship('NodeClass', primaryjoin = ('NodeClass.left_id == NodeClass.node_id'), use_list=False)
    right = relationship('NodeClass', primaryjoin = ('NodeClass.right_id == NodeClass.node_id'), use_list=False)

Problem

What if I had something like a double linked list in a relational database, for example: ``` node_id left_id right_id 1 null 2 2 1 3 3 2 null ``` Then I have some SQLAlchemy code like the following: ``` class NodeClass(Base): __tablename__ = 'nodes_table' node_id = Column(Integer, primary_key=True) left_id = Column(Integer, ForeignKey('nodes_table.node_id')) right_id = Column(Integer, ForeignKey('nodes_table.node_id')) left = relationship('NodeClass') # Wrong right = relationship('NodeClass') # Wrong ``` If I have node_id 2, and I call `NodeClass.left` I would like to receive node_id 1 in return. How can I configure the SQLAlchemy relationships to behave this way? UPDATE: I will give a second example. Consider a table of people, and each person has a mother and a father. ``` person_id mother_id father_id 1 null null 2 null null 3 1 2 ``` The SQLAlchemy code: ``` class PersonClass(Base): __tablename__ = 'persons_table' person_id = Column(Integer, primary_key=True) mother_id = Column(Integer, ForeignKey('persons_table.person_id')) father_id = Column(Integer, ForeignKey('persons_table.person_id')) mother = relation('PersonClass') # Wrong father = relation('PersonClass') # Wrong ```

Original source