How do you dynamically adjust recursion depth for active load in SQLAlchemy ORM?

Question

I have a hierarchical setup with two tables, where table A refers to table B, which then refers to another record in table A, etc ... but only to the specified recursion depth.

This works fine for me, using SQLAlchemy and declarative. I also successfully use energetic loading with lazy and join_depth properties in table relations. This is consistent with the SQLAlchemy documentation .

However, this layout captures the depth of the recursion with " join_depth " once during program loading ... but using the data that I use. I know that the recursion depth I have to use every time. How to change the recursion depth used for each request?

I looked at how to use the master join_depth property on the ORM base object, but this will not work, since I have a multi-threaded scoped_session application where it would be dangerous (not to mention that this parameter is quite difficult to find in SQLAlchemy in lead time!).

I also looked at using joinedload with the request, but can't see how to change the depth with this.

I also know the SQL WITH RECURSIVE syntax available in some databases via CTE , but as far as possible, I want to avoid this for now, as some databases still do not support it (and SQLAlchemy is also not, at least not on at the moment and not without a large set of dialects).

Answer 1

There is no official way to do this, but after the code, the following solution was created for me. I am using the Node example from docs that 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 the time of creation, the children property is set as join_depth of 2. This initial value is written to Node.children.property.join_depth . However, changing this value will do nothing. Upon initialization, the relationships create a “strategy” for the join, and this copies the value of join_depth . To change the connection depth of a strategy for a 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 installing Node.children.property.strategy.join_depth number of connections in the generated request also changes.