computeNbResult throw error when the query use a groupBy

[VincentLanglet]

Environment

Sonata packages

$ composer show --latest 'sonata-project/*'
# Put the result here.
sonata-project/admin-bundle              3.54.1 3.57.0 The missing Symfony Admin Generator
sonata-project/block-bundle              3.18.2 4.1.0  Symfony SonataBlockBundle
sonata-project/cache                     2.0.1  2.0.1  Cache library
sonata-project/core-bundle               3.17.2 3.17.2 Symfony SonataCoreBundle (abandoned)
sonata-project/datagrid-bundle           2.5.0  3.1.1  Symfony SonataDatagridBundle
sonata-project/doctrine-extensions       1.5.1  1.5.1  Doctrine2 behavioral extensions
sonata-project/doctrine-orm-admin-bundle 3.12.0 3.13.0 Symfony Sonata / Integrate Doctrine ORM into the SonataAdminBundle
sonata-project/exporter                  2.0.1  2.0.1  Lightweight Exporter library

Subject

When writing a custom filter like this

$queryBuilder->andHaving("SUM($contractSupportAlias.euroAmount) $operator :$parameterName");
$queryBuilder->groupBy($alias);
$queryBuilder->setParameter($parameterName, $value['value'] * 100);

Or like this

$queryBuilder->andHaving("MAX($relaunchAlias.creationDate) < :$parameterName");
$queryBuilder->groupBy($alias);
$queryBuilder->setParameter($parameterName, $date->format('Y-m-d H:i:s'));

Or again like this

$queryBuilder->andHaving("COUNT(DISTINCT $relaunchAlias) $operator :$parameterName");
$queryBuilder->groupBy($alias);
$queryBuilder->setParameter($parameterName, $value['value']);

With a LEFT_JOIN and a GROUP BY, we get an error

The query returned multiple rows. Change the query or use a different result function like getScalarResult().

It's coming from the computeNbResult.

public function computeNbResult()
{
    $countQuery = clone $this->getQuery();

    if (\count($this->getParameters()) > 0) {
        $countQuery->setParameters($this->getParameters());
    }

    $countQuery->select(sprintf(
        'count(%s %s.%s) as cnt',
        $countQuery instanceof ProxyQuery && !$countQuery->isDistinct() ? null : 'DISTINCT',
        current($countQuery->getRootAliases()),
        current($this->getCountColumn())
    ));

    return (int) ($countQuery->resetDQLPart('orderBy')->getQuery()->getOneOrNullResult(Query::HYDRATE_SINGLE_SCALAR));
}

I tried to return 0 inside and my query works. The error is just coming from computeNbResult.

[phansys]

The concerns about this behavior seem to be addressed at #501.

[VincentLanglet]

Indeed it seems kinda-related. But if adding a resetDQLPart('groupBy') can help for the listing, i think it will give bad results for filtering.

@greg0ire You may still have advice to provide.

[greg0ire]

Sorry but this is way too old :sweat_smile:

[VincentLanglet]

I tested in my project

return array_sum(array_column(
            ($countQuery->resetDQLPart('orderBy')->getQuery()->getResult(Query::HYDRATE_SCALAR)),
            'cnt'
        ));

It's not the best but it fix the issue.

[Tofandel]

The PR doesn't entirely solve the issue if the select contains aggregate columns (eg: SELECT SUBSTR(created_at, 1, 7) as month FROM foo GROUP BY month would fail because month is removed from the select