subquery加入doctrine dql [英] subquery in join with doctrine dql

问题描述

我想使用DQL在SQL中创建一个如下所示的查询：

  select 
 e。 * 
从
e 
内部连接（
选择
 uuid，max（locale）作为区域设置
从
e 
其中
 locale ='nl_NL'或
 locale ='nl'
 group by 
 uuid 
）作为e_ on e.uuid = e_.uuid和e.locale = e_。我尝试使用QueryBuilder生成查询和子查询。我认为他们自己做正确的事情，但我不能把它们结合在连接语句中。现在有人可以用DQL吗？我不能使用本地SQL，因为我想返回真实的对象，我不知道该查询运行的对象（我只知道具有uuid和locale属性的基类）。
  $ subQueryBuilder = $ this-> _em-> createQueryBuilder（）; 
 $ subQueryBuilder 
  - > addSelect（'e.uuid，max（e.locale）as locale'）
  - > from（$ this-> _entityName，'e​​'） 
  - > where（$ subQueryBuilder-> expr（） - > in（'e.locale'，$ localeCriteria））
  - > groupBy（'e.uuid'）; 
 
 $ queryBuilder = $ this-> _em-> createQueryBuilder（）; 
 $ queryBuilder 
  - > addSelect（'e'）
  - > from（$ this-> _entityName，'e​​'）
  - > join（'（ '。$ subQueryBuilder。'）as'，'e_'）
  - > where（'e.uuid = e_.uuid'）
  - > andWhere（'e.locale = e_.locale ）; 
  
 
 
解决方案
您不能将子查询放在你的DQL条款。
 
 
 
我会假设你的PK是 {uuid，locale} code>，在与IRC讨论中。由于您的查询中还有两个不同的列，这可能会变得丑陋。 
你可以做的是将它放入 WHERE 子句中：
 从
选择
e 
 MyEntity e 
 WHERE 
 e.uuid IN（
 select 
 e2.uuid 
 from 
 MyEntity e2 
其中
 e2.locale IN（：selectedLocales）
 group by 
 e2.uuid 
）
 AND e.locale IN （
从
中选择
 max（e3.locale）作为区域设置
 MyEntity e3 
其中
 e3.locale IN（：selectedLocales）
 group by 
 e3.uuid 
）
  
请注意，我使用了一个比较针对您绑定到：selectedLocales 的（非空）区域设置数组。这是为了避免破坏查询缓存，如果要匹配其他区域设置。
 
 
 
如果没有真正的优势，我也不建议使用查询构建器这样做，因为如果您动态添加条件（也是涉及到3个查询构建器！），则只会使查询缓存更加简单。
 
I want to use DQL to create a query which looks like this in SQL:
select
    e.*
from
    e
inner join (
    select
        uuid, max(locale) as locale
    from
        e
    where
        locale = 'nl_NL' or
        locale = 'nl'
    group by
        uuid
) as e_ on e.uuid = e_.uuid and e.locale = e_.locale
I tried to use QueryBuilder to generate the query and subquery. I think they do the right thing by them selves but I can't combine them in the join statement. Does anybody now if this is possible with DQL? I can't use native SQL because I want to return real objects and I don't know for which object this query is run (I only know the base class which have the uuid and locale property).
    $subQueryBuilder = $this->_em->createQueryBuilder();
    $subQueryBuilder
        ->addSelect('e.uuid, max(e.locale) as locale')
        ->from($this->_entityName, 'e')
        ->where($subQueryBuilder->expr()->in('e.locale', $localeCriteria))
        ->groupBy('e.uuid');

    $queryBuilder = $this->_em->createQueryBuilder();
    $queryBuilder
        ->addSelect('e')
        ->from($this->_entityName, 'e')
        ->join('('.$subQueryBuilder.') as', 'e_')
        ->where('e.uuid = e_.uuid')
        ->andWhere('e.locale = e_.locale');

解决方案
You cannot put a subquery in the FROM clause of your DQL.

I will assume that your PK is {uuid, locale}, as of discussion with you on IRC. Since you also have two different columns in your query, this can become ugly.
What you can do is putting it into the WHERE clause:
select
    e
from
    MyEntity e
WHERE
    e.uuid IN (
        select
            e2.uuid
        from
            MyEntity e2
        where
            e2.locale IN (:selectedLocales)
        group by
            e2.uuid
    )
    AND e.locale IN (
        select
            max(e3.locale) as locale
        from
            MyEntity e3
        where
            e3.locale IN (:selectedLocales)
        group by
            e3.uuid
    )
Please note that I used a comparison against a (non empty) array of locales that you bind to to the :selectedLocales. This is to avoid destroying the query cache if you want to match against additional locales.

I also wouldn't suggest building this with the query builder if there's no real advantage in doing so since it will just make it simpler to break the query cache if you add conditionals dynamically (also, it's 3 query builders involved!)

                        
这篇关于subquery加入doctrine dql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！