SQLALchemy adjacency list get all parents

Question

Here's an adjacency list example:

class TreeNode(Base):
    __tablename__ = 'tree'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(id))
    name = Column(String(50), nullable=False)

    children = relationship("TreeNode",
                        cascade="all",
                        backref=backref("parent", remote_side=id)
                    )

Supposing I've got a simple linear structure: (0)---->(1)---->(2)---->(3)

How do I get all ancestor nodes of a certain node? Something like node2.parents.all() that returns a list of nodes 0 and 1.

I tried to do this:

parents = relationship("TreeNode", cascade="all", primaryjoin="TreeNode.parent_id==TreeNode.id")

with no luck - it returns children instead of parents.

Thanks.

Answer 1

Thank you, I'll look it up - for now it seems to be a little bit dark. If someone else's stumbled on this, it's possible to use rather more exprensive thing which still does what I want:

@property
def parents(self):
    allparents = []
    p = self.parent
    while p:
        allparents.append(p)
        p = p.parent
    return allparents