SQL-Query PageTree TYPO3 CMS

sqlTreeQueryTYPO3.php

/**
 * custom sql to query pageTree
 * efficient to query large trees (>30.000 records)
 *
 * returns flat array with page records
 *
 * @param int $uid uid of page to start with
 * @param string $where optional where query part to limit results
 * @param int $limit optional limit for query
 * @return array selected records
 */
public function getFlattenedPageTree(int $uid, string $where = '', int $limit = 0): array
{
    $sql = 'WITH RECURSIVE page_tree (uid, pid, level, tstamp) AS (
        SELECT uid, pid, 0, tstamp FROM pages WHERE uid = ? AND deleted = 0
        UNION SELECT
            child.uid AS uid,
            child.pid AS pid,
            pt.level + 1 AS level,
            child.tstamp as tstamp,
        FROM pages AS child, page_tree AS pt
        WHERE pt.uid = child.pid AND child.deleted=0 AND child.doktype != 254
        )';
    $sql .= 'SELECT * FROM page_tree';
    if ( $where != '') {
        $sql .= ' WHERE ' . $where;
    }
    $sql .= ' ORDER by tstamp DESC';
    if ( $limit > 0) {
        $sql .= ' LIMIT ' . $limit;
    }
    $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('pages');
    $result = $connection->fetchAllAssociative($sql, [$uid], [ParameterType::INTEGER]);
    return $result;
}

// usage example getFlattenedPageTree($uid=<X>, $where = 'level > 1')