ap2i/src/Repository/Organization/OrganizationRepository.php

<?php
declare(strict_types=1);

namespace App\Repository\Organization;

use App\Entity\Organization\Organization;
use App\Enum\Organization\PrincipalTypeEnum;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\Persistence\ManagerRegistry;

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

    /**
     * Vérifie si l'organisation est un dernier parent : possède des enfants mais ces enfants ne possèdent pas d'enfant
     * @param Organization $organization
     * @return bool
     */
    public function isLastParent(Organization $organization): bool {

        $sql = sprintf("
        SELECT 
            IF( (SELECT o.id 
                    FROM Organization as o 
                    WHERE o.id=neto.organization_id
                    AND o.principalType IN('%s','%s','%s','%s','%s','%s')) IS NOT NULL ,0,1) AS is_last_parent
        FROM
            NetworkOrganization as neto
        WHERE
            neto.parent_id = %d
            and (neto.endDate is null or neto.endDate = \"0000-00-00\" or neto.endDate > CURDATE())
        GROUP BY is_last_parent
        ORDER BY is_last_parent DESC",
            PrincipalTypeEnum::NATIONAL_FEDERATION(),
            PrincipalTypeEnum::REGIONAL_FEDERATION(),
            PrincipalTypeEnum::LOCAL_FEDERATION(),
            PrincipalTypeEnum::GROUPMENT(),
            PrincipalTypeEnum::DEPARTEMENTAL_FEDERATION(),
            PrincipalTypeEnum::DELEGATION(),
            $organization->getId()
        );

        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('is_last_parent', 'is_last_parent', 'integer');
        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
        $result = $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

        foreach ($result as $a_tmp) {
            if ($a_tmp['is_last_parent']) {
                return true;
            }
        }

        return false;
    }
}