GINO equivalence of SQLAlchemy CTE

cte.py

import asyncio
from gino import Gino
from sqlalchemy import Column

db = Gino()


class Node(db.Model):
    __tablename__ = 'node'
    id = Column(db.Integer, primary_key=True)
    property_1 = Column(db.Text)
    property_2 = Column(db.Integer)

    async def descendant_nodes(self):
        assoc_alias = Association.alias()
        descendants = db.select([
            Node.id,
            Node.property_1,
            (self.property_1 + '/' + Node.property_1).label('path')
        ]).where(Node.id == assoc_alias.child_id).where(
            assoc_alias.parent_id == self.id
        ).cte(recursive=True)

        node_alias = Node.alias()
        assoc_alias_2 = Association.alias()
        descendants = descendants.union(
            db.select([
                node_alias.id,
                node_alias.property_1,
                (descendants.c.path + '/' + node_alias.property_1).label('path')
            ]).select_from(
                node_alias.join(
                    assoc_alias_2, node_alias.id == assoc_alias_2.child_id
                ).join(
                    descendants, descendants.c.id == assoc_alias_2.parent_id
                )
            )
        )
        query = db.select(
            [descendants.c.property_1, descendants.c.path]
        ).select_from(descendants)
        return await query.gino.all()


class Association(db.Model):
    __tablename__ = 'association_table'
    parent_id = Column(db.Integer, db.ForeignKey('node.id'))
    child_id = Column(db.Integer, db.ForeignKey('node.id'))


async def main():
    await db.set_bind('postgresql://localhost/postgres')

    b = await db.select(Node).where(
        Node.property_1 == 'b'
    ).gino.load(Node).first()
    for item in await b.descendant_nodes():
        print(item)


asyncio.get_event_loop().run_until_complete(main())