TYPO3 Core

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.