Project

General

Profile

Actions

Bug #93576

open

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

Added by Michael Grundkötter over 3 years ago. Updated over 3 years ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
Extbase
Target version:
-
Start date:
2021-02-24
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
11
PHP Version:
7.4
Tags:
Complexity:
Is Regression:
Sprint Focus:

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.


Files

Typo3DbBackend-getObjectCountByQuery.patch (1.15 KB) Typo3DbBackend-getObjectCountByQuery.patch example patch file for TYPO3 v8 Michael Grundkötter, 2021-02-24 07:53

Related issues 3 (1 open2 closed)

Related to TYPO3 Core - Bug #80464: count() not working correctly in Extbase when using custom statementNew2017-03-27

Actions
Related to TYPO3 Core - Bug #79330: Extbase getObjectCountByQuery returns wrong resultsClosedMorton Jonuschat2017-01-15

Actions
Related to TYPO3 Core - Bug #79322: Invalid SQL Statement created in Extbase getObjectCountByQuery()ClosedMorton Jonuschat2017-01-15

Actions
Actions

Also available in: Atom PDF