SQLALchemy adjacency list get all parents -

here's 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 simple linear structure: (0)---->(1)---->(2)---->(3)

how ancestor nodes of node? node2.parents.all() returns list of nodes 0 , 1.

i tried this:

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

with no luck - returns children instead of parents.

thanks.

you can not using simple relationship. if use mssql or postgresql, instead try create (hybrid) attribute, leverage on query.cte.