Issue Bug Under Review Orphaned

#93576Typo3DbBackend::getObjectCountByQuery does not work correctly with custom doctrine queries

Michael Grundkötter 02/24/21, 07:50 updated 03/25/25, 21:40

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

History 610

  1. 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>

  2. Stefan Froemken commented <span data-processor="relativetime" data-value="2025-03-25T16:06:40+00:00" > 03/25/25, 16:06 </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

  3. 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.