Construct the entire tree from a SQLAlchemy adjacency list relationship

Question

I have a class Node with a self referential mapping 'children' (backref 'parent') representing a tree in SQLAlchemy and I want to select the entire tree. If I do

session.query(Node).all()

then every access to node.children triggers a select. If I do a joined load

session.query(Node).options(joinedload_all('children')).all()

then the sql issued has an unnecessary table join since I want the entire tree (all nodes) anyways. Is there a way to do this in SA or should I just construct the tree on my own outside of SA?

Answer 1

There is no problem with parent property, since all information needed is already loaded in the object. SQLAlchemy just have to look for parent object in the session and issue query only when it's missing. But this doesn't work for children: the library can't be sure all children objects are already in the session. So you can construct the tree yourself and instruct SQLAlchemy to use this data via set_committed_value:

from collections import defaultdict
from sqlalchemy.orm.attributes import set_committed_value

nodes = session.query(Node).all()

# Collect parent-child relations
children = defaultdict(list)
for node in nodes:
    if node.parent:
        children[node.parent.id].append(node)

# Set collected values
for node in nodes:
    set_committed_value(node, 'children', children[node.id])