Many-to-many self-referential relationship in sqlalchemy

Question

I'm trying to make a self-referential many-to-many relationship (it means that Line can have many parent lines and many child lines) in sqlalchemy like this:

Base = declarative_base()

class Association(Base):
 __tablename__ = 'association'

 prev_id = Column(Integer, ForeignKey('line.id'), primary_key=True)                            
 next_id = Column(Integer, ForeignKey('line.id'), primary_key=True)


class Line(Base):
 __tablename__ = 'line'

 id = Column(Integer, primary_key = True)
 text = Column(Text)
 condition = Column(Text)
 action = Column(Text)

 next_lines = relationship(Association, backref="prev_lines")



class Root(Base):
 __tablename__ = 'root'

 name = Column(String, primary_key = True)
 start_line_id = Column(Integer, ForeignKey('line.id'))

 start_line = relationship('Line')

But I get the following error: sqlalchemy.exc.ArgumentError: Could not determine join condition between parent/ child tables on relationship Line.next_lines. Specify a 'primaryjoin' expressio n. If 'secondary' is present, 'secondaryjoin' is needed as well.

Do you know how I could remedy this?

Answer 1

You should just need:

prev_lines = relationship(
    Association,
    backref="next_lines",
    primaryjoin=id==Association.prev_id)

Since this specifies the next_lines back reference there is no need to have a next_lines relationship.

You can also do this using the remote_side parameter to a relationship: http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py