Marcel Djaman Marcel Djaman - 6 months ago 53
PHP Question

Subquery in doctrine2 notIN Function

I'd like to select members who are not in specific service. I have 3 tables :


  • membre

  • service

  • membre_service
    (relation between
    membre
    and
    service
    )



I'm using doctrine 2 and in SQL my query is :

SELECT m.* FROM membre m WHERE m.`id` NOT IN (
SELECT ms.membre_id FROM membre_service ms WHERE ms.service_id != 29
)


In Doctrine, I do :

$qb = $this->_em->createQueryBuilder();
$qb2 = $qb;
$qb2->select('m.id')
->from('Custom\Entity\MembreService', 'ms')
->leftJoin('ms.membre', 'm')
->where('ms.id != ?1')
->setParameter(1, $service);

$qb = $this->_em->createQueryBuilder();
$qb->select('m')
->from('Custom\Entity\Membre', 'm')
->where($qb->expr()->notIn('m.id', $qb2->getDQL())
);
$query = $qb->getQuery();
//$query->useResultCache(true, 1200, __FUNCTION__);

return $query->getResult();


I got the following error :


Semantical Error] line 0, col 123 near 'm WHERE ms.id': Error: 'm' is already defined.

Answer

The same alias cannot be defined 2 times in the same query

$qb  = $this->_em->createQueryBuilder();
$qb2 = $qb;
$qb2->select('m.id')
    ->from('Custom\Entity\MembreService', 'ms')
    ->leftJoin('ms.membre', 'm')
    ->where('ms.id != ?1');

$qb  = $this->_em->createQueryBuilder();
$qb->select('mm')
    ->from('Custom\Entity\Membre', 'mm')
    ->where($qb->expr()->notIn('mm.id', $qb2->getDQL())
);
$qb->setParameter(1, $service);
$query  = $qb->getQuery();

return $query->getResult();

Ideally you should use many-to-many relation for your entity, in this case your query is going to be much simpler.