[BUG] ConfigurationPageResolver::getClosestPageIdWithActiveTemplate relies on IN() to force a sort order (which it does not) [WITH FIX] #4012
Description
Describe the bug
The method returns whatever the query returns as first row. This is not guaranteed to be the first value in the list within ÌN()`.
Expected behavior
The page id that really is the first one with a sys_template record within the rootline.
To Reproduce
I'm not sure how an arbitrary result can be reproduced.
I noticed the behaviour when I initialized the queue for a site that is inside another one (microsite). Also the outer site is inside a page that holds a sys_template record (not a site itself, just a page to hold TypoScript defaults for a number of sites).
Some suggest to use FIELD() but I don't know if this will work with DBAL/Doctrine:
SELECT * FROM t WHERE x IN (3,2,1) ORDER BY FIELD(x,3,2,1)
So I prose a less concise fix.
Solution
public function findOneClosestPageIdWithActiveTemplateByRootLine(array $rootLine)
{
$rootLinePageIds = [];
$orderColumn = '(CASE pid';
foreach ($rootLine as $index => $rootLineItem) {
$rootLinePageId = (int)$rootLineItem['uid'];
$rootLinePageIds[] = $rootLinePageId;
$orderColumn .= ' WHEN ' . $rootLinePageId . ' THEN ' . $index;
}
$orderColumn .= ' END) AS sort_index';
$queryBuilder = $this->getQueryBuilder();
$result = $queryBuilder
->select('uid', 'pid')
->addSelectLiteral($orderColumn)
->from($this->table)
->where($queryBuilder->expr()->in('pid', $rootLinePageIds))
->orderBy('sort_index', 'DESC') // rootline indices are reversed
->execute()->fetch();
return isset($result['pid']) ? $result['pid'] : 0;
}