CODEWITHSUNDEEP
phpsqlsymfonydoctrine-ormdoctrine
Desi Cochrane
Desi Cochrane

Reputation: 657

Doctrine query "where notIn" subquery issue

In Symfony2, I have a many:many relationship between users and roles. I am trying to get a list of all the users which are not linked to the ROLE_SUPER_ADMIN role.

Before migrating to Symfony2/Doctrine, I had achieved this with a simple NOT IN sql query, but for the life of me I can't achieve the same effect with doctrine.

Here is what I am trying:

$em = $this->getDoctrine()->getManager();
$qb = $em->createQueryBuilder();
$qb2 = $qb;

$dql = $qb->select('sa.id')
          ->from('AcmeAdminBundle:User', 'sa')
          ->leftJoin('sa.roles', 'r')
          ->andWhere('r.role = :role')
          ->getDQL();

$result = $qb2->select('u')
              ->from('AcmeAdminBundle:User', 'u')
              ->where($qb2->expr()->notIn('u.id', $dql))
              ->setParameter('role', 'ROLE_SUPER_ADMIN')

$users = $result->getQuery()->getResult();

But this is the error:

[Semantical Error] line 0, col 140 near 'sa LEFT JOIN': 
Error: 'sa' is already defined.

And this is the output:

SELECT u 
FROM AcmeAdminBundle:User sa 
LEFT JOIN sa.roles r, AcmeAdminBundle:User u 
WHERE u.id NOT IN (
                   SELECT sa.id 
                   FROM AcmeAdminBundle:User sa 
                   LEFT JOIN sa.roles r 
                   WHERE r.role = :role
                  )

No idea why it is outputting like that as it should not be performing LEFT JOIN two times, my suspicion is that it has something to do with having two QueryBuilder instances, but could be something else entirely.

Upvotes: 2

Views: 2258

Answers (1)

Rene Terstegen
Rene Terstegen

Reputation: 8046

You need the MEMBER OF or in your case NOT MEMBER OF option.

$qb->select('sa.id')
   ->from('AcmeAdminBundle:User', 'sa')
   ->where(":Role NOT MEMBER OF sa.roles")
   ->setParameter("Role", <<ROLE_ID_OR_ROLE_ENTITY>>);

I didn't test this code, but it should give you some idea.

Full documentation can be found at https://www.doctrine-project.org/projects/doctrine-orm/en/2.14/reference/dql-doctrine-query-language.html

Upvotes: 5

Related Questions