SQLAlchemy - how to set relationship to back_populates to parent id in the same table

Question

I try to set fk which parent_id contains id of a person in People table in orm manner and backpopulate between them but it does not work.

class People(Base):
    __tablename__ = "people"

    id = Column(Integer, primary_key=True)
    name = Column(String(20), nullable=False, unique=True)
    parent_id = Column(Integer, ForeignKey('people.id'))
    parent = relationship("People", back_populates="parent", uselist=False)

engine = create_engine(
    f'mssql://{username}:{password}@{server_name}/{db_name}?driver=SQL+Server&trusted_connection=yes')

Session = sessionmaker(bind=engine)
session = Session()
session.add(People(name='me'))

raise sa_exc.ArgumentError( sqlalchemy.exc.ArgumentError: People.parent and back-reference People.parent are both of the same direction symbol('ONETOMANY'). Did you mean to set remote_side on the many-to-one side ?

You may want to [review what `back_populates`](https://stackoverflow.com/questions/51335298/concepts-of-backref-and-back-populate-in-sqlalchemy) does. In any case, it looks like you are trying to build an adjacency list type, like [this very example on [sqlalchemy's documentation](https://docs.sqlalchemy.org/en/14/_modules/examples/adjacency_list/adjacency_list.html). — metatoaster, Jun 20 '22 at 03:06

score 0 · Answer 1 · answered Jun 20 '22 at 12:00

You can use the remote_side argument.

Here's code I'm using adapted to your example:

class People(Base):
    __tablename__ = "people"

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('people.id'))

    parent = relationship('People', foreign_keys=parent_id, remote_side=id)
    children = relationship('People', back_populates='parent')

SQLAlchemy - how to set relationship to back_populates to parent id in the same table

1 Answers1