Use correct COUNT on queries build with TYPO3 QueryBuilder. Solve queries containing JOIN. Tag: ONLY_FULL_GROUP_BY

QueryResultHelper.php

<?php

declare(strict_types=1);

/*
 * This file is part of the package jweiland/yellowpages2.
 *
 * For the full copyright and license information, please read the
 * LICENSE file that was distributed with this source code.
 */

namespace JWeiland\Yellowpages2\Helper;

use Doctrine\DBAL\Driver\Exception;
use Doctrine\DBAL\Exception as DBALException;
use TYPO3\CMS\Core\Context\Context;
use TYPO3\CMS\Core\Context\Exception\AspectNotFoundException;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;
use TYPO3\CMS\Core\Database\Query\Restriction\WorkspaceRestriction;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Persistence\QueryInterface;
use TYPO3\CMS\Extbase\Persistence\QueryResultInterface;

/**
 * If you're working with Extbase's native Query object the Typo3DbQueryParser will automatically switch to
 * SELECT(DISTINCT table.uid) while COUNTing on JOINed queries. See its property: $this->suggestDistinctQuery.
 *
 * But if you're building queries with TYPO3`s own QueryBuilder and sending them to the Typo3DbBackend of Extbase,
 * it does not check for JOINed tables, which leads to false COUNT statement. Because of MySQL/MariaDB option
 * ONLY_FULL_GROUP_BY we had to add all possible columns to GROUP BY. With just COUNT(*) you will get a list of all
 * records where count column results to 1 each. That will break pagination.
 *
 * As the result is the same we set COUNT(DISTINCT) to all with TYPO3's QueryBuilder generated queries.
 *
 * See: ONLY_FULL_GROUP_BY
 * BUGFIX: https://review.typo3.org/c/Packages/TYPO3.CMS/+/59641
 * REVERTED: https://review.typo3.org/c/Packages/TYPO3.CMS/+/66395
 */
class QueryResultHelper
{
    public function getCountFromQueryResult(QueryResultInterface $queryResult): int
    {
        $query = $queryResult->getQuery();
        $queryBuilder = $this->getQueryBuilderReadyForCount($query);
        if ($queryBuilder instanceof QueryBuilder) {
            try {
                $count = $queryBuilder->executeQuery()->fetchOne();
            } catch (DBALException | \Doctrine\DBAL\DBALException | Exception $e) {
                return 0;
            }

            return $this->getUpdatedCountByOffsetLimit($count, $query);
        }

        return count($queryResult);
    }

    private function getQueryBuilderReadyForCount(QueryInterface $query): ?QueryBuilder
    {
        $queryBuilder = clone $query->getStatement()->getStatement();
        if (!$queryBuilder instanceof QueryBuilder) {
            return null;
        }

        $source = $queryBuilder->getQueryPart('from')[0];
        $tableName = $source['alias'] ?: $source['table'];
        $fieldName = $queryBuilder->quoteIdentifier('uid');

        // COUNT(*) would be also OK, if you don't have any HAVING clauses.
        $queryBuilder
            ->resetQueryPart('groupBy')
            ->resetQueryPart('orderBy')
            ->selectLiteral(sprintf('COUNT(DISTINCT %s.%s)', $tableName, $fieldName));

        try {
            // Ensure to count only records in the current workspace
            $context = GeneralUtility::makeInstance(Context::class);
            $workspaceUid = (int)$context->getPropertyFromAspect('workspace', 'id');
            $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(WorkspaceRestriction::class, $workspaceUid));
        } catch (AspectNotFoundException $e) {
        }

        return $queryBuilder;
    }

    private function getUpdatedCountByOffsetLimit(int $count, QueryInterface $query): int
    {
        if ($query->getOffset()) {
            $count -= $query->getOffset();
        }

        if ($query->getLimit()) {
            $count = min($count, $query->getLimit());
        }

        return $count;
    }
}