Multiple self referential relationships in SQLAlchemy

Question

I have a database model where I need a one-to-many relationship and two one-to-one relationships. Here's the model i've made, but it's throwing 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 whenever i try to do anything to 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's really weird is that it worked without the next and prev columns. Anybody know what's wrong?

Answer 1

The topic is old, but since this is so confusing i'll write it down.
You don't need separate 'prev' column, you already have it as backref for 'next'. Also, since you have multiple foreign keys to the same target, you need to specify primary joins 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 couple of bugs or just bits of weird behavior i noticed:
- You can only use remote_side="Page.id", not remote_side=[id] and not remote_side=["Page.id"], or it won't work (sqlalchemy 0.6.6). This was annoying to pin down.
- It seem like you should always use remote_side with primary key, regardless of what you actual remote side is. remote_side="Pages.next_id" will always generate a weird error, even if it seems appropriate.
- primaryjoin expression is confusing as hell, as it doesn't use aliases, but this is actually the correct way to do it. The binding engine knows which expression to replace with a parameter (which is way too implicit and against the 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))