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;
    }

Possible duplicate of [Doing a WHERE .. IN subquery in Doctrine 2](https://stackoverflow.com/questions/6637506/doing-a-where-in-subquery-in-doctrine-2) — rkeet, Aug 02 '19 at 12:21

Jakumi · Accepted Answer · 2019-08-02T14:19:28.070

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 ...?)

You are right, it's a much simpler syntax. (`WHERE am.roles LIKE '%ROLE_PUBLISHER%'`should be `'%ROLE_MANAGER%')`. Tx !! — Rmy5, Aug 02 '19 at 09:57

score 0 · Answer 2 · answered Aug 02 '19 at 09:05

0

According to DQL query examples section within Doctrine's DQL documentation you need to either use EXISTS keyword within DQL query or use exists() method of Expr class.

answered Aug 02 '19 at 09:05

Flying

4,422
2
17
25

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

2 Answers2

Linked