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.

score 62 · Accepted Answer · edited Aug 12 '12 at 23:32

62

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.

edited Aug 12 '12 at 23:32

Marcel Djaman

1,276
1
17
34

answered Aug 12 '12 at 20:44

WizardZ

1,372
13
12

Thank for your answer! MembreService is already in many-to-many... You really helped me out! And see my edit for the final answer. May God Bless You... – Marcel Djaman Aug 12 '12 at 23:03
If you are using parameters in the subquery: $qb->setParameters($qb2->getParameters()); is needed, though you might want to $qb->setParameters(array_merge($qb2->getParameters(),$qb->getParameters())) to avoid overriding parameters from the exterior query. – Omn Mar 04 '16 at 01:32
Hmmm... take my last comment with a grain of salt, that only appears to work in some versions of doctrine... in other versions setParameters replaces rather than adds to the parameters... – Omn Mar 04 '16 at 02:27
Hi, little confused as to why you instantiate `$qb` twice, once on line 1 and again on line 8? Is that necessary or just a typo? – RiggsFolly Feb 09 '18 at 10:17

score 16 · Answer 2 · answered Jul 17 '13 at 12:39

16

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

answered Jul 17 '13 at 12:39

Comstar

1,256
11
8

Thanks for this. I was looking for a way to do this with different repositories, your solution did the trick. – mason81 Sep 23 '14 at 19:58
In `$q->expr()`, what is `$q` ? – psylosss Aug 10 '16 at 14:28
1

$q is an instance of QueryBuilder. The correct code would look like this: ```$q = $this->createQueryBuilder('m'); $q->where($q->expr()->notIn()....))``` – versedi Mar 25 '17 at 14:30
You forget quotes in where clause, please add it. – JuliSmz Apr 19 '20 at 19:57

score 6 · Answer 3 · edited May 31 '16 at 15:30

6

You can use (NOT) MEMBER OF:

<?php
$query = $em->createQuery('SELECT m.id FROM Custom\Entity\Membre WHERE :service NOT MEMBER OF m.services');
$query->setParameter('service', $service);
$ids = $query->getResult();

See the documentation for more examples.

edited May 31 '16 at 15:30

rogaa

370
2
16

answered Aug 29 '14 at 10:02

Stephan Vierkant

9,674
8
61
97

Subquery in doctrine2 notIN Function

3 Answers3

Linked