Symfony6原生查询返回空数组问题排查求助

我是Symfony 6.1新手，自定义原生SQL查询时遇到返回空数组的问题，尝试多种方法仍未解决。需求是通过GROUP_CONCAT关联查询Mission和Tag数据，暂不想用数据库视图实体，希望排查NativeQuery的问题。

相关代码 #

AccueilProspectorController代码 #

<?php

namespace App\Controller;

use App\Entity\Mission;
use App\Entity\Tag;
use App\Entity\User;
use App\Form\AddMissionFormType;
use App\Form\RegistrationFormType;
use App\Repository\MissionRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Symfony\Component\Security\Core\Security;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

class AccueilProspectorController extends AbstractController
{
    #[Route('/accueil/prospector', name: 'app_accueil_prospector')]
    public function index(Request $request,ManagerRegistry $doctrine,Security $security): Response
    {
        $mission = new Mission();

        //Récupération de toutes les missions.
        $allmission = $doctrine->getManager()->getRepository(Mission::class)->selectmissionswithtags();

        //Création du formulaire pour ajouter une mission

        $mission->setIduser($security->getUser());
        $form = $this->createForm(AddMissionFormType::class, $mission)->handleRequest($request);

        if($form->isSubmitted() && $form->isValid()){
            $entityManager = $doctrine->getManager();
            $entityManager->persist($mission);
            $entityManager->flush();
            return $this->redirectToRoute('app_accueil_prospector');
        }

        return $this->render('accueil_prospector/index.html.twig', [
            'controller_name' => 'AccueilProspectorController',
            'addmissionForm' => $form->createView(),
            'missionsvalues' => $allmission,
        ]);
    }
}

MissionRepository代码 #

<?php

namespace App\Repository;

use App\Entity\Mission;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @extends ServiceEntityRepository<Mission>
 *
 * @method Mission|null find($id, $lockMode = null, $lockVersion = null)
 * @method Mission|null findOneBy(array $criteria, array $orderBy = null)
 * @method Mission[]    findAll()
 * @method Mission[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class MissionRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Mission::class);
    }

    public function save(Mission $entity, bool $flush = false): void
    {
        $this->getEntityManager()->persist($entity);

        if ($flush) {
            $this->getEntityManager()->flush();
        }
    }

    public function remove(Mission $entity, bool $flush = false): void
    {
        $this->getEntityManager()->remove($entity);

        if ($flush) {
            $this->getEntityManager()->flush();
        }
    }

    public function selectmissionswithtags(){
        $sql = "SELECT descriptionmission,onsetdate,deadline,prioritymission,remote, GROUP_CONCAT(tg.nomtag SEPARATOR ',') as tag From mission m
                left join mission_tag mt on m.id = mt.mission_id
                left join tag tg on mt.tag_id = tg.id
                GROUP BY descriptionmission;";
        $rsm = new ResultSetMapping();
        $rsm->addEntityResult(Mission::class, 'mission');
        $em = $this->getEntityManager();
        return $result = $em->createNativeQuery($sql,$rsm)->getArrayResult();
    }
}

问题原因及解决方案 #

核心问题 #

1. ResultSetMapping未配置字段映射：仅添加addEntityResult但未指定字段与实体属性的对应关系，Doctrine无法解析查询结果，返回空数组。
2. GROUP BY子句不规范：仅按descriptionmission分组可能导致数据错误（不同Mission可能有相同描述），且不符合SQL标准（非聚合字段需在GROUP BY中）。
3. 聚合字段未映射：GROUP_CONCAT的结果未映射到实体属性或标量结果，无法被正确获取。

修正后的代码 #

修改MissionRepository中的selectmissionswithtags方法：

public function selectmissionswithtags()
{
    // 按Mission主键分组，确保每个任务唯一，符合SQL规范
    $sql = "SELECT 
                m.id, 
                m.descriptionmission, 
                m.onsetdate, 
                m.deadline, 
                m.prioritymission, 
                m.remote, 
                GROUP_CONCAT(tg.nomtag SEPARATOR ',') as tags 
            FROM mission m
            LEFT JOIN mission_tag mt ON m.id = mt.mission_id
            LEFT JOIN tag tg ON mt.tag_id = tg.id
            GROUP BY m.id;";

    $rsm = new ResultSetMapping();
    // 关联实体与查询中的表别名
    $rsm->addEntityResult(Mission::class, 'm');
    // 逐个映射实体属性与查询字段
    $rsm->addFieldResult('m', 'id', 'id');
    $rsm->addFieldResult('m', 'descriptionmission', 'descriptionmission');
    $rsm->addFieldResult('m', 'onsetdate', 'onsetdate');
    $rsm->addFieldResult('m', 'deadline', 'deadline');
    $rsm->addFieldResult('m', 'prioritymission', 'prioritymission');
    $rsm->addFieldResult('m', 'remote', 'remote');
    // 映射聚合字段为标量结果，后续可手动赋值给实体
    $rsm->addScalarResult('tags', 'tags');

    $em = $this->getEntityManager();
    $query = $em->createNativeQuery($sql, $rsm);
    $results = $query->getResult();

    // 手动将聚合字段赋值给Mission实体（需确保实体有对应的属性和setter）
    foreach ($results as $item) {
        if ($item[0] instanceof Mission) {
            $mission = $item[0];
            $mission->setTags($item['tags']);
        }
    }

    return array_column($results, 0); // 返回纯实体数组
}

补充说明 #

• 字段映射必须完整：所有需要填充到实体的字段都要通过addFieldResult映射，否则Doctrine会忽略该字段。
• GROUP BY主键：按Mission的id分组，确保每个任务只返回一条记录，避免分组逻辑错误。
• 标量结果处理：GROUP_CONCAT的结果是标量值，用addScalarResult映射后，getResult()会返回包含实体和标量值的数组，需手动提取并赋值。
• 调试技巧：可以先执行$query->getSQL()获取最终执行的SQL，在数据库客户端运行验证是否有数据返回，排查数据层面问题。

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