Empty array from query native builder symfony6

Question

I'm new on symfony 6.1 and i would like to understand what wrong with my custom sql request. I try many things but with no success can you help me ?

This is my Accueil Controller where i want to get back the sql result from my repository :

<?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,
        ]);
    }
}

This is my repository where is the request :

<?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();
    }

This is my selectmissionwithtags witch return empty array.

enter image description here

Querybuilder -> i don't have GROUP_CONCAT so i can't use this kind of query builder. NativeQuery -> i use this method for the moment.

The last thing that i can do is to create entity for an database view and create the entity related to. But i would like to understand the querynative method for the moment.

Many thanks ;)

Answer 1

The ResultSetMapping needs to be really explicit to work, you'd have to do something like

    $rsm->addEntityResult(Mission::class, 'mission');
    $rsm->addFieldResult('mission', 'id', "id");
    //same for all fields
    $rsm->addJoinedEntityResult(MissionTag::class, 'mt', 'mission', 'mission_tag');
    $rsm->addFieldResult('mt', 'id', 'id);
    //same for all fields of related entities

But there is ResultSetMappingBuilder to the rescue

    $rsmb->addRootEntityFromClassMetadata(Mission::class);

That's all you should have to do, with doctrine handling the relation and hydration. It will try to hydrate from your SELECT tho, so you need all fields used in the hydrator in the SELECT from your query.

You can specify the joined relation with:

    $rsm->addJoinedEntityFromClassMetadata(MissionTag::class, 'mt', 'mission', 'relationName', array('id' => "tag_id"));

But then you need the join in the query (it will look for the relation in the select) and you need to be careful to when to use the doctrine name (relationName) and when to use the database name (tag_id)

You could also archived your ends by adding GROUP_CONCAT to doctrine, by leveraging Doctrine's User Defined Functions

I've found a snippet doing exactly that, it's not mine and I have not tested it.