#93576Typo3DbBackend::getObjectCountByQuery does not work correctly with custom doctrine queries
Description
When you build custom queries in extbase repositories, the method Typo3DbBackend::getObjectCountByQuery does not work correctly any more.
In my example, we have geo data in the database and we calculate a rough distance to a given point with an extbase repository query using doctrine. This looks something like:
$queryBuilder
->join(
'foobar',
ZipCode::TABLE_NAME,
'zip',
'zip.uid = foobar.zip_code'
)
->addSelectLiteral('(' . self::EARTH_RADIUS . ' * acos(cos(radians( ' . $regionalObject->getLat() . ' )) * cos(radians( zip.lat )' .
') * cos(radians( zip.lng ) - radians( ' . $regionalObject->getLng() . ' )) + sin(radians( ' . $regionalObject->getLat() .
' )) * sin(radians( zip.lat )))) AS distance')
->andHaving(
$queryBuilder->expr()->lte('distance', $filterDemand->getMaxDistance())
);
$filterDemand->setSortBy('distance');
$filterDemand->setSortDir('ASC');
Now this works as expected for normal queries and output in extbase/fluid. But as soon as we use the fluid count viewhelper or the pagination widget , the getObjectCountByQuery method gets called and this error happens:
#1472074379: Unknown column ‘distance’ in ‘having clause’
The reason is the mentioned method just throws away all the defined select parts of the query and replaces them with a single
@$queryBuilder->count(‘*’);@ which is not expected. The count is expected to happen on the query result of the original query, so the code should do something like this instead:
$fullSql = $queryBuilder->getSQL();
$queryBuilder
->resetQueryParts()
->selectLiteral('count(*)')
->getConcreteQueryBuilder()->from("({$fullSql})", 'temporary')
;
This is how the doctrine ORM does the paging as well (subquery) instead of fumbling around in the original query. The code is the same from TYPO3 versions 8 to 11.
Reviews 1
Related issues 3
History 610
-
Michael Grundkötter changed Related to #80464 <span data-processor="relativetime" data-value="2021-02-24T07:50:47+00:00" > 02/24/21, 07:50 </span>
-
Michael Grundkötter changed Related to #79330 <span data-processor="relativetime" data-value="2021-02-24T07:50:58+00:00" > 02/24/21, 07:50 </span>
-
Michael Grundkötter changed Related to #79322 <span data-processor="relativetime" data-value="2021-02-24T07:51:09+00:00" > 02/24/21, 07:51 </span>
-
Michael Grundkötter changed attached Typo3DbBackend-getObjectCountByQuery.patch <span data-processor="relativetime" data-value="2021-02-24T07:54:03+00:00" > 02/24/21, 07:54 </span>
-
Stefan Froemken commented <span data-processor="relativetime" data-value="2025-03-25T16:06:40+00:00" > 03/25/25, 16:06 </span>
-
Gerrit Code Review pushed patch set 1 for main to change 88753 status
NewUnder Review <span data-processor="relativetime" data-value="2025-03-25T16:43:11+00:00" > 03/25/25, 16:43 </span> -
Stefan Froemken commented <span data-processor="relativetime" data-value="2025-03-25T16:45:25+00:00" > 03/25/25, 16:45 </span>
I have taken your patch and modified it for Doctrine >=4.0 where no resetQueryParts is available.
-
Gerrit Code Review pushed patch set 2 for main to change 88753 <span data-processor="relativetime" data-value="2025-03-25T17:39:03+00:00" > 03/25/25, 17:39 </span>
-
Gerrit Code Review pushed patch set 3 for main to change 88753 <span data-processor="relativetime" data-value="2025-03-25T21:38:26+00:00" > 03/25/25, 21:38 </span>
-
Gerrit Code Review pushed patch set 4 for main to change 88753 <span data-processor="relativetime" data-value="2025-03-25T21:40:38+00:00" > 03/25/25, 21:40 </span>
This query looks very similar to my query from maps2 ;-)
I had exactly the same problem and documented it in code, and “solved” it my own way:
https://github.com/jweiland-net/maps2/blob/main/Classes/Domain/Repository/PoiCollectionRepository.php#L121-L128