如何解决Sonata Admin中使用groupby时的‘查询返回多行’错误

Let's break down what's happening here: Sonata Admin's list view expects one entity object per row in the query result. When you add a GROUP BY clause, your query starts returning aggregated groups (each group might map to multiple original entities), and Doctrine ORM can't convert those grouped results back to a single entity instance—hence the error prompting you to use a scalar result function like getScalarResult().

Here are two tailored solutions depending on your actual goal:

Solution 1: Show one entity per city (e.g., first/latest record for each city) #

If you just want to eliminate duplicate city entries and display a single entity per city, adjust your query to select only one valid entity per grouped city instead of aggregating. Use a subquery to pick the minimum or maximum ID per city:

public function createQuery($context = 'list') {
    $query = parent::createQuery($context);
    $rootAlias = $query->getRootAliases()[0];
    $entityClass = get_class($this->getSubject());

    // Subquery to get the ID of the first record per city
    $subQuery = $query->getEntityManager()->createQueryBuilder()
        ->select('MIN(c.id)')
        ->from($entityClass, 'c')
        ->groupBy('c.cityId');

    // Filter main query to only include those unique IDs
    $query->andWhere("$rootAlias.id IN (:uniqueCityIds)")
          ->setParameter('uniqueCityIds', $subQuery->getQuery()->getResult());

    return $query;
}

This way, your query returns valid single entities, and Sonata Admin can render the list normally without conflicts.

Solution 2: Display aggregated statistics (e.g., record count per city) #

If you want to show grouped metrics (like how many records exist per city), you'll need to switch to scalar results and customize your list fields to handle non-entity data:

First, modify your query to select aggregated values:

public function createQuery($context = 'list') {
    $query = parent::createQuery($context);
    $rootAlias = $query->getRootAliases()[0];

    // Select city ID and count of records per city
    $query->select("$rootAlias.cityId, COUNT($rootAlias.id) as recordCount")
          ->groupBy("$rootAlias.cityId");

    return $query;
}

Then update your configureListFields to display the scalar data. You can use a simple field or add a custom template for formatting:

protected function configureListFields(ListMapper $listMapper)
{
    $listMapper
        ->add('cityId', null, ['label' => 'City ID'])
        ->add('recordCount', null, [
            'label' => 'Total Records',
            // Optional: Uncomment to use a custom template for styling
            // 'template' => '@YourAdminBundle/List/record_count.html.twig'
        ]);
}

Note: When using scalar results, Sonata Admin won't map data to your entity, so ensure the selected fields match exactly what you add to the list.

Key Takeaway #

The error stems from Sonata Admin's list view being built to work with individual entities—your GROUP BY broke that expectation. Pick the solution that aligns with whether you need to display full entities or aggregated statistics.

内容的提问来源于stack exchange，提问作者Pavel