与学说 dql 连接的子查询 [英] subquery in join with doctrine dql

问题描述

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

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

我尝试使用 QueryBuilder 来生成查询和子查询.我认为他们自己做了正确的事情，但我不能在 join 语句中将它们结合起来.如果 DQL 可以做到这一点，现在有人吗?我不能使用本机 SQL，因为我想返回真实对象，但我不知道该查询针对哪个对象运行(我只知道具有 uuid 和 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');

推荐答案

您不能在 DQL 的 FROM 子句中放置子查询.

You cannot put a subquery in the FROM clause of your DQL.

我假设您的 PK 是 {uuid, locale}，正如在 IRC 上与您讨论的那样.由于您的查询中还有两个不同的列，这可能会变得很难看.您可以做的是将其放入 WHERE 子句中:

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
    )

请注意，我使用了与绑定到 :selectedLocales 的(非空)语言环境数组进行比较.这是为了避免在您想匹配其他语言环境时破坏查询缓存.

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.

如果这样做没有真正的优势，我也不建议使用查询构建器来构建它，因为如果您动态添加条件，它只会更容易破坏查询缓存(另外，它涉及 3 个查询构建器！)

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!)

这篇关于与学说 dql 连接的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！