WHERE ...在Doctrine queryBuilder或同等功能中的带子查询的IN查询

问题描述

在我的Symfony 4项目中，我有一个 User 实体和一个 UserRepository .我正在尝试在 QueryBuilder (第2章)或什至在 DQL 中实现等效于此 SQL 查询.

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.

我尝试了不同的方法，但无法弄清楚如何在子查询中编写WHERE ... IN.

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

推荐答案

您的查询可以转换为没有子查询的内容，但是可以使用联接，而联接应该是等效的(并且应该具有相同的运行时/复杂度)

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%'

可以相应地转换为querybuilder(我必须假设您没有定义关联...我觉得很烦，但是您可能有自己的理由):

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

实际上还有使用子查询，但是使用子查询imho总是很不方便-而且很丑.

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

(您可能只想编写普通的DQL查询，您可能会感到宾至如归...?)

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

这篇关于WHERE ...在Doctrine queryBuilder或同等功能中的带子查询的IN查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！