SQLAlchemy: How to join a second table to a hierarchical table and make the join available to all levels of the query?

Question

I'm try to join a second table to hierarchical table in SQLAlechemy, and have the results of that join available to all levels the query.

class NodeType(Base):
    id = Column(Integer, primary_key=True)
    node_type = Column(Text)
    node = relationship("Node", back_populates="node_type")


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", lazy="joined", join_depth=2)
    node_type = relationship("NodeType", back_populates="node")

# Nested structure I'm looking for but lacking joins
session.query(Node).all()

# Join is only to the parent, not any of the children or grandchildren
session.query(Node).join(NodeType).all()

I've reviewed the documentation on adjacency lists.. Tried many different methods of loading a second table with no results. I'm obviously missing something.

Answer 1

Setting the lazy to joined on the node_type relationship solves this problem.

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", lazy="joined", join_depth=2)
node_type = relationship("NodeType", lazy="joined", back_populates="node")