cte.py

def get_children_containers(dbsession, folder_id, max_depth=None):
    root = dbsession.query(
        Folder, sql.literal(1, type_=Integer).label('level')
    ).filter(
        sql.and_(
            Folder.exclude_nav == False,
            Folder.container_id == folder_id
        )
    ).cte(
        name='parents', recursive=True
    )

    filters = [
        Folder.exclude_nav == False,
        Folder.container_id != None
    ]

    if max_depth:
        filters.append(
            root.c.level <= max_depth
        )

    filters = sql.and_(*filters)

    root = root.union_all(
        dbsession.query(Folder, root.c.level + 1).join(
            root, root.c.id == Folder.container_id
        ).filter(filters))

    tabs = dbsession.query(
        Folder
    ).join(
        root, root.c.id == Folder.id
    ).add_columns(
        root.c.level.label('level')
    ).order_by(
        root.c.level, root.c.container_id, root.c.weight.desc()
    ).all()


    return FolderHierarchy(tabs)