How to implement recursive reading of all descendants of an object in Gino()?

Question

Python

The base is designed so that each table element can have one or more child elements of the same table. Please help with the implementation of a database query that would return children, children of children, and so on - that is, all descendants of any element. The option to get the id of the child elements of this from the database, and then send requests to the database again to get the children of these children, etc. I don’t really like it because of the resource intensity. asyncpg, which gino works with, is faster than executing python code, so it makes more sense to build a recursive database query.
I know that this task is easily solved in sqlalchemy. An example of its solution is given for example here, however I need the request in gino. Gino, as far as I understand, does not support the use of the relationship method, instead, such requests are implemented somehow using loaders. the official manual has an example of using the load method to retrieve all immediate children of a given element here . But I don't know how to use it in solving my problem of recursively finding all children of a given parent.
In fact, the ideal solution would not even be the solution given in the link , but a solution using a slightly different data structure, if possible.
Namely, the following

class Nodes(db.Model):
    __tablename__ = 'nodes'

    id = db.Column(db.Integer, primary_key=True)
    info = db.Column(db.Text)

class Edges(db.Model):
    __tablename__ = 'edges'

    id = db.Column(db.Integer, primary_key=True)
    source = db.Column(db.Integer, db.ForeignKey('nodes.id'))
    target = db.Column(db.Integer, db.ForeignKey('nodes.id'))

    _edge_idx = db.Index('edge_idx', 'source', 'target', unique=True)

Instead of storing a list of immediate children in the Nodes table, it would be ideal for me to describe the relationships of the elements in the edges table, and use a recursive query to find all rows in the Edges table such that the target parameter in them would be given (it stores the id node of the graph to which it is connected), then apply the same query to the source parameters of the received rows in Edges - that is, it would look for all rows in the Edges table whose target parameter would match the previously received values ​​of the source parameter and return their parameter values source , and so on, until there are no rows left that have the target parameter equal to one of the received values ​​of the source parameter in the previous iteration.
That is, I much more like the option of searching for descendants using an additional table that describes the relationships of nodes in the graph.
I'm asking for help, because I myself can't seem to come up with a solution.

Answer 1

To search for parent-children, there is Self Referencing , though experimental.