A few self-referencing relationships in SQLAlchemy

Question

I have a database model where I need a one-to-many relationship and two one-to-one relationships. Here is the model I made, but it throws errors

class Page(Base): __tablename__ = 'pages' id = Column(Integer, primary_key=True) title = Column(String(100), nullable=False) content = Column(Text, nullable=False) parent_id = Column(Integer, ForeignKey("pages.id"), nullable=True) children = relationship("Page", backref=backref("parent", remote_side=id)) next_id = Column(Integer, ForeignKey("pages.id"), nullable=True) next = relationship("Page", backref=backref("prev", remote_side=id, uselist=False)) prev_id = Column(Integer, ForeignKey("pages.id"), nullable=True) prev = relationship("Page", backref=backref("next", remote_side=id, uselist=False)) def __init__(self, title, content, parent_id=None, next_id=None, prev_id=None): self.title = title self.content = content self.parent_id = parent_id self.next_id = next_id self.prev_id = prev_id def __repr__(self): return '<Page "%r">' % self.title 

I get the following error when I try to do something in the database

 ArgumentError: Could not determine join condition between parent/child tables on relationship Page.children. Specify a 'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is needed as well. 

What is really strange is that it worked without the following and previous columns. Does anyone know what happened?

Answer 1

The topic is old, but since it is so confusing, I will write it down. You do not need a separate column "prev", you already have it as a backref for "next". In addition, since you have several foreign keys for the same purpose, you need to specify primary connections manually:

 class Page(Base): __tablename__ = 'pages' id = Column(Integer, primary_key=True) title = Column(String(100), nullable=False) content = Column(Text, nullable=False) parent_id = Column(Integer, ForeignKey("pages.id"), nullable=True) parent = relationship("Page", primaryjoin=('pages.c.id==pages.c.parent_id'), remote_side='Page.id', backref=backref("children" )) next_id = Column(Integer, ForeignKey("pages.id"), nullable=True) next = relationship("Page", primaryjoin=('pages.c.next_id==pages.c.id'), remote_side='Page.id', backref=backref("prev", uselist=False)) 

A few errors or just a bit of strange behavior I noticed:
- You can use only remote_side="Page.id" , not remote_side=[id] , not remote_side=["Page.id"] , or it will not work (sqlalchemy 0.6.6). It was unpleasant to joke.
- It seems that you should always use remote_side with the primary key, no matter what the actual remote side is. remote_side="Pages.next_id" always generates a strange error, even if it seems appropriate.
- expressionjoin an expression is confusing because it does not use aliases, but it’s actually the right way to do it. The binding mechanism knows which expression to replace with a parameter (which is too implicit and against Zen, btw).

Answer 2

You can use foreign_keys :

 class Page(Base): __tablename__ = 'pages' id = Column(Integer, primary_key=True) title = Column(String(100), nullable=False) content = Column(Text, nullable=False) parent_id = Column(Integer, ForeignKey("pages.id"), nullable=True) parent = relationship("Page", foreign_keys=[parent_id], remote_side=[id], backref=backref("children" )) next_id = Column(Integer, ForeignKey("pages.id"), nullable=True) next = relationship("Page", foreign_keys=[next_id], remote_side=[id], backref=backref("prev", uselist=False))