WHERE ... IN query with sub-query in Doctrine queryBuilder or equivalent

Question

In my Symfony 4 project I have a User entity and a UserRepository. I'm trying to implement the equivalent of this SQL query in the QueryBuilder(Doctrine 2) or even in DQL.

SELECT * FROM user WHERE account_manager_id IN (SELECT id FROM user WHERE account_manager_id = :managerAdminId AND roles LIKE '%ROLE_MANAGER%')

Or maybe use a different syntax.

I tried different things, but couldn't figure out how to write the WHERE ... IN with the sub-query.

This is all I could come up with, which I don't like because it fires multpiple queries for something I could do with a single one:

    //App\Repository\UserRepository

    public function getPublishersOfManagers($managerAdminId)
    {
        //SELECT * FROM user WHERE account_manager_id IN (SELECT id FROM user WHERE account_manager_id = :managerAdminId AND roles LIKE '%ROLE_MANAGER%')

        $managerIds = $this->createQueryBuilder('u')
                    ->select('u.id')
                    ->where('u.roles LIKE :role')
                    ->setParameter('role' , '%ROLE_MANAGER%')
                    ->andWhere('u.accountManager = :managerAdminId')
                    ->setParameter('managerAdminId' , $managerAdminId)
                    ->getQuery()->getArrayResult();

        $publishers = [];

        foreach ($managerIds as $id) {

            $publishers[] = $this->createQueryBuilder('u')
                    ->select('u')
                    ->where('u.roles LIKE :role')
                    ->setParameter('role' , '%ROLE_PUBLISHER%')
                    ->andWhere('u.accountManager = :managerAdminId')
                    ->setParameter('managerAdminId' , $id)
                    ->getQuery()->getResult();
        }

        return $publishers;
    }

Answer 1

your query can be turned into something without a sub-query, but with a join instead, which should be equivalent (and should have the same runtime/complexity)

SELECT u 
FROM user u 
LEFT JOIN user am ON (am.id=u.accountManager) 
WHERE am.roles LIKE '%ROLE_MANAGER%' 
  AND am.accountManager=:managerAdminId
  AND u.roles LIKE '%ROLE_PUBLISHER%'

which can be translated into querybuilder accordingly (I have to assume, that you did not define your associations ... which I find disturbing, but you probably have your reasons):

return $this->createQueryBuilder('u')
  ->leftJoin('App\Entity\User', 'am', 'WITH', 'am.id=u.accountManager')
  ->andWhere('am.roles LIKE :role')
  ->setParameter('role', '%ROLE_MANAGER%')
  ->andWhere('am.accountManager = :managerAdminId')
  ->setParameter('managerAdminId', $managerAdminId)
  ->andWhere('u.roles LIKE :role2')
  ->setParameter('role2', '%ROLE_PUBLISHER%')
  ->getQuery()->getResult();

there is also the options of actually using sub-queries, but using sub-queries imho is always inconvenient - and ugly.

(you might have a look into writing just plain DQL queries, you might feel more at home ...?)