Bug #92514
openSELECT DISTINCT and ORDER BY in Extbase query lead to database error when using PostgreSQL
0%
Description
Model Lorem
has a property $ipsum
for a 1:1 relation to Model Ipsum
, which has a property $name
.
Model Lorem
has a property $dolors
for a n:1 relation to Model Dolor
, which has a property $lorem
and a property $code
The query
class LoremRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {
public function findLoremFilteredByDolorsOrderedByIpsum()
$query = $this->createQuery();
return $query
->matching($query->in('dolors.code', ['A', 'B']))
->setOrderings(['ipsum.name' => 'ASC'])
->execute();
}
}
is translated by Extbase to the following SQL query
SELECT DISTINCT tx_my_lorem.*
FROM tx_my_lorem
LEFT JOIN tx_my_dolor ON tx_my_lorem.uid = tx_my_dolor.lorem
LEFT JOIN tx_my_ipsum ON tx_my_lorem.ipsum = tx_my_ipsum.uid
WHERE tx_my_dolor.code IN ('A','B')
ORDER BY tx_my_ipsum.name;
This query works on MySQL but fails on PostgreSQL (and probably SQLite, MSSQL as well) with a Doctrine\DBAL\Exception\DriverException
: ORDER BY items must appear in the select list if SELECT DISTINCT is specified
.
This happens because the DISTINCT
result is sorted by tx_my_ipsum.name
, a column which is not retrieved in SELECT
.
Inextbase/Classes/Persistence/Generic/Storage/Typo3DbQueryParser.php
for $columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_MANY)
and $columnMap->getTypeOfRelation() === ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY)
the property $this->suggestDistinctQuery = true;
is set.
This is used in the method getObjectDataByQuery()
of extbase/Classes/Persistence/Generic/Storage/Typo3DbBackend.php
by checking isDistinctQuerySuggested()
to add DISTINCT
to the query: $selectParts[0] = 'DISTINCT ' . $selectParts[0];
Extbase adds the DISTINCT
because requesting a Lorem
filtered by multiple Dolor@s could lead to the same @Lorem
being returned multiple times which is not wanted in Extbase context when constituting objects.
The sorting by tx_my_ipsum.name
is the wanted and correct behaviour.
In case Extbase adds the DISTINCT
to the query it must also SELECT
the ORDER BY
columns, even if it does not use them (as a random column name?).
When selecting the ORDER BY
columns care must be taken this random column name does not interfere with the columns of lorem
SELECT DISTINCT tx_my_lorem.*, tx_my_ipsum.name AS some_random_string_which_does_not_interfere_with_lorem_columns
FROM tx_my_lorem
LEFT JOIN tx_my_dolor ON tx_my_lorem.uid = tx_my_dolor.lorem
LEFT JOIN tx_my_ipsum ON tx_my_lorem.ipsum = tx_my_ipsum.uid
WHERE tx_my_dolor.code IN ('A','B')
ORDER BY tx_my_ipsum.name;
This query works on PostgreSQL as well.