Как построить рекурсивный запрос в SQLAlchemy?

Question

[av-reznikov]

Есть таблица:

class AddressElement(Base):
    __tablename__ = 'TELEMENT'

    id = sa.Column('ID', sa.String(255), unique=True)
    parent_id = sa.Column('PARENT_ID', sa.String(255),  sa.ForeignKey('TELEMENT.ID'))
    level = sa.Column('LEVEL', sa.BigInteger,  sa.ForeignKey(AddressElementLevel.outer_id))
    parent = relationship('AddressElement', backref=backref('children'), remote_side=[id])

В запросе приходит parent_id (уровня level 1 или 2), необходимо построить запрос, который рекурсивно достанет все дочерние сущности уровней 3,4, 5, 6
Задачу реализовал на Питоне, но выполняется очень долго, и если детей больше 1000 - запрос падает с ошибкой.

#функция получения идентификаторов детей (ее требуется заменить на cte запрос sqlalchemy)
    def get_children_ids(address: AddressElement) -> list:
        res = []
        for child in address.children:
            if child.level in [3, 4, 5, 6]:
                res.append(child.id)
                res.extend(get_children_ids(child))
        return res

    def get_results(parent_id):
        # Получение родителя
        parent_address_element = session.query(AddressElement).filter(
            AddressElement.global_id == parent_id, AddressElement.level.in_([1, 2])
        ).one()
        # Получение id детей этого родителя
        children_ids = get_children_ids(parent_address_element)

        result = session.query(AddressElement).filter(
            AddressElement.id.in_(children_ids)  # В этом месте падает если кол-во children_ids > 1000
        )
        # Дальше различные фильтрации результатов

Answer 1

[VasylKolomiets]

думаю, вы уже нашли ответ. он в плоскости перевода рекурсии из Python по капот SQLAlchemy.
вот статьи:
рекурсия в SQL
рекурсивные СТЕ в SQLAlchemy. найти параграф cte(name=None, recursive=False)