Subquery in doctrine2 notIN Function

Question

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 1

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.

Answer 2

Actually if you are using the Symfony2 repository class you could also do the following:

$in = $this->getEntityManager()->getRepository('Custom:MembreService')     ->createQueryBuilder('ms')     ->select('identity(ms.m)')     ->where(ms.id != ?1);  $q = $this->createQueryBuilder('m')     ->where($q->expr()->notIn('m.id', $in->getDQL()))     ->setParameter(1, $service);  return $q->getQuery()->execute();