How do you dynamically adjust the recursion depth for eager loading in the SQLAlchemy ORM?

Question

I have a two-table hierarchical setup where table A references table B, which then references back to a different record in table A, and so on... but only up to a given recursion depth.

I have this working well using SQLAlchemy and declarative. I'm also successfully using eager loading with the lazy and join_depth properties on the table relations. This is as per the SQLAlchemy documentation.

However, this arrangement fixes the recursion depth at 'join_depth' once at program load time... but with the data I am using I know the recursion depth I should be using each time. How can I change the recursion depth used on a per-query basis?

I've considered fiddling with the master join_depth property on the base ORM object, but this won't work since I've got a multi-threaded scoped_session application where that would be dangerous (not to mention the fact that the parameter is pretty hard to locate within SQLAlchemy at runtime!).

I've also looked at using joinedload with the query but don't see how to vary the depth with that.

I'm also aware of the 'WITH RECURSIVE' SQL syntax available in some databases via CTEs, but as great as it is, I want to avoid that for the moment since some DBs still don't support it (and neither does SQLAlchemy - at least not at the moment and not without a lot of dialect customization).

Answer 1

There's no official way to do this, but following the code produced the following solution for me. I'm using the Node example from the docs you linked.

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)

At creation time, the children property is given a join_depth of 2. This initial value is recorded in Node.children.property.join_depth. However, changing this value will not do anything. At init, the relationship creates a "strategy" for joining, and this copies the join_depth value. To change the join depth of the strategy for the relationship, you set Node.children.property.strategy.join_depth.

>>> engine.echo = True  # print generated queries
>>> session.query(Node).all()  # with default join_depth
SELECT node.id AS node_id, node.parent_id AS node_parent_id, node.data AS node_data, node_1.id AS node_1_id, node_1.parent_id AS node_1_parent_id, node_1.data AS node_1_data, node_2.id AS node_2_id, node_2.parent_id AS node_2_parent_id, node_2.data AS node_2_data FROM node LEFT OUTER JOIN node AS node_2 ON node.id = node_2.parent_id LEFT OUTER JOIN node AS node_1 ON node_2.id = node_1.parent_id
>>> Node.children.property.strategy.join_depth = 4  # new join depth
>>> session.query(Node).all()  # with new join depth
SELECT node.id AS node_id, node.parent_id AS node_parent_id, node.data AS node_data, node_1.id AS node_1_id, node_1.parent_id AS node_1_parent_id, node_1.data AS node_1_data, node_2.id AS node_2_id, node_2.parent_id AS node_2_parent_id, node_2.data AS node_2_data, node_3.id AS node_3_id, node_3.parent_id AS node_3_parent_id, node_3.data AS node_3_data, node_4.id AS node_4_id, node_4.parent_id AS node_4_parent_id, node_4.data AS node_4_data FROM node LEFT OUTER JOIN node AS node_4 ON node.id = node_4.parent_id LEFT OUTER JOIN node AS node_3 ON node_4.id = node_3.parent_id LEFT OUTER JOIN node AS node_2 ON node_3.id = node_2.parent_id LEFT OUTER JOIN node AS node_1 ON node_2.id = node_1.parent_id

After setting Node.children.property.strategy.join_depth, the number of joins in the generated query changes as well.