Query node children with arbitrary depth

query_children.py

def query_children(context, levels=3, more_conditions=None):
    selects = []

    for level in range(levels):
        alias = Node.__table__
        conditions = [Node.__table__.c.id == context.id]
        for depth in range(level + 1):
            alias, old_alias = Node.__table__.alias(), alias
            conditions.append(alias.c.parent_id == old_alias.c.id)
        if more_conditions:
            for key, value in more_conditions.items():
                conditions.append(getattr(alias.c, key) == value)
        selects.append(select([alias.c.id], and_(*conditions)))

    sel_union = selects.pop(0).alias().select()
    for sel in selects:
        sel_union = sel_union.union(sel).alias().select()

    return DBSession.query(Node).filter(Node.id.in_(sel_union))