[Python] How do I select a recursive entity in sqlalchemy

I am saving a graph to a database. It has a structure like:

class SuperNode(Base):
__tablename__ = "supernodes"

id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)

nodes = relationship("Node", back_populates="supernode")


class Node(Base):
__tablename__ = "nodes"

id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
supernode_id = Column(Integer, ForeignKey("supernodes.id"), nullable=False)
sub_node_id = Column(Integer, ForeignKey("supernodes.id"), nullable=True)

supernode = relationship("SuperNode", back_populates="nodes")
sub_node = relationship("SuperNode", foreign_keys=[sub_node_id])

children = relationship(
"Node",
secondary=association_table,
primaryjoin=id == association_table.c.parent_id,
secondaryjoin=id == association_table.c.child_id,
backref="parents"
)

association_table = Table(
"parent_child_association",
Base.metadata,
Column("parent_id", Integer, ForeignKey("nodes.id"), primary_key=True),
Column("child_id", Integer, ForeignKey("nodes.id"), primary_key=True),
)



FYI: I am using sqlalchemy 2.0 with aiosqlite

Now, I have trouble creating a statement that will get be a SuperNode by id, and then the entire hierarchy of nodes their sub_nodes the nodes of those... etc. so I get everything in the hierarchy of a SuperNode when I select it. I've been playing with CTE's, but with that I can end up getting tonnes of rows, and I have to rebuild the structure (which is acceptable as plan B, but I still have issues getting SuperNode.nodes.sub_node.nodes and so on), the other option would be using selectinload, but that will get me only a fixed number of layers, which is just not good enough.

CTE's seems like a way to do this, perhaps the best way? But I can't quite seem to understand how to use them in a case like this, and I have not been able to find any good examples.

So if you can tell me how, what, and why, I would be very happy indeed

Continue reading...