How would I query using sqlalchemy to retrieve all objects from an adjacency list?

Question

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    children = relationship("Node",
                backref=backref('parent', remote_side=[id])
            )

When using this type of model, how could I query for all linked objects of a parent?

In other words, I need a list that contains all the children and children of children, and children of children of children...

Traversing trees can be done using a recursive CTE: https://stackoverflow.com/questions/52408644/how-can-i-write-a-sqlalchemy-query-that-will-return-all-descendants-of-a-node-in, https://stackoverflow.com/questions/11994092/how-can-i-perform-this-recursive-common-table-expression-in-sqlalchemy — Ilja Everilä, Oct 07 '19 at 18:26

score 2 · Answer 1 · answered Oct 09 '19 at 14:41

2

This is what I ended up doing, probably not the best, but it worked.

pks = []

def get_pk(o):
    pks.append(o.pk)
    for a in o.attachments:
        get_pk(a)

get_pk(parent)

answered Oct 09 '19 at 14:41

Corey

816
9
19

score 1 · Answer 2 · answered Oct 07 '19 at 16:24

Query children on your node object directly. Check out lazy parameter in sqlalchemy documentation. That will determine, when exactly your list will be queried (with object itself or separately)

See below code for reference

parent = Node.query.filter_by(id=1).first()
children_list = n.children

P.S.: children_list will have immediate children only. To get grand-children and further, query again and again as needed, check lazy parameter for more information

How would I query using sqlalchemy to retrieve all objects from an adjacency list?

2 Answers2