Feature #99320
Updated by Sascha Egerer over 1 year ago
As far is i can see all database engines supported by TYPO3 do support common table expressions.
These expressions can be used to fetch recursive data form the database like we have in our page tree.
I've implemented this in one of our projects (TYPO3 10 and now 11) a year ago and it runs pretty good.
With this query we can reduce the number of queries in the backend a lot. In my case it was 25.000 (!) queries which is now... around 3 queries. We had huge performance issues due to the infrastructure setup and a backend page load took 2 minutes after the project was moved to this hoster. So i had a look and implemented the CTE for this function and the page load took only 10 seconds after this change. So this could be a huge performance boost at some places.
My TYPO3 11 patch as reference (without permClause support...)
<pre>
# Use a Common Table Expressions to fetch the recursive page tree instead
# of executing a new query for each page.
# This happens often in backend views like if you edit a recipe page.
# Before this change there are around 25.000 Sql calls from this method
# which is reduced to a few with this query. There could also be a runtime
# cache added but this may introduces other problems.
--- Classes/Database/QueryGenerator.php 2021-04-13 09:16:54.000000000 +0200
+++ Classes/Database/QueryGenerator.php 2021-05-11 10:49:28.000000000 +0200
@@ -1575,44 +1575,27 @@
public function getTreeList($id, $depth, $begin = 0, $permClause = '')
{
$depth = (int)$depth;
- $begin = (int)$begin;
$id = (int)$id;
- if ($id < 0) {
- $id = abs($id);
- }
- if ($begin === 0) {
- $theList = $id;
- } else {
- $theList = '';
- }
- if ($id && $depth > 0) {
+
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
- $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
- $queryBuilder->select('uid')
- ->from('pages')
- ->where(
- $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($id, Connection::PARAM_INT)),
- $queryBuilder->expr()->eq('sys_language_uid', 0)
+ // Use common table expression to fetch the page tree
+ $pages = $queryBuilder->getConcreteQueryBuilder()->getConnection()->fetchAllAssociative(
+ 'WITH RECURSIVE cte AS (
+ SELECT uid, pid, deleted, 1 AS level
+ FROM pages
+ WHERE uid = ' . $id . ' AND deleted = 0 AND sys_language_uid = 0
+
+ UNION ALL
+ SELECT p.uid, p.pid, p.deleted, c.level + 1
+ FROM cte c
+ JOIN pages p ON p.pid = c.uid
+ WHERE p.deleted = 0 AND sys_language_uid = 0 AND c.level < ' . $depth . '
)
- ->orderBy('uid');
- if ($permClause !== '') {
- $queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($permClause));
- }
- $statement = $queryBuilder->execute();
- while ($row = $statement->fetchAssociative()) {
- if ($begin <= 0) {
- $theList .= ',' . $row['uid'];
- }
- if ($depth > 1) {
- $theSubList = $this->getTreeList($row['uid'], $depth - 1, $begin - 1, $permClause);
- if (!empty($theList) && !empty($theSubList) && ($theSubList[0] !== ',')) {
- $theList .= ',';
- }
- $theList .= $theSubList;
- }
- }
- }
- return $theList;
+ SELECT uid,pid,deleted,level
+ FROM cte
+ ORDER BY level;'
+ );
+ return implode(',', array_column($pages, 'uid'));
}
/**
</pre>