问题描述

我有一个 DQL，如下所示:

I have a DQL as like below:

SELECT at, count(at.id) FROM AccountTriple at JOIN at.property p JOIN at.account ac WHERE p.create_analytics = '1' GROUP BY at.property, at.value, ac.service

如您所见，它具有三个连接.因为'at'和'ac'都有大量的数据.为了优化它，我试图将加入前的p.create_analytics = '1'"检查移至ac"，以提供较小的数据集以供加入.我正在努力实现这样的目标:

As you can see, it has three joins. As 'at' and 'ac' both have large amount of data. In attempt to optimize it, I am trying to move the "p.create_analytics = '1'" checking before join to 'ac' to give it a smaller data set to join. I am trying to achieve something like this:

SELECT at, count(at.id) FROM ( SELECT at FROM AccountTriple at JOIN at.property p WHERE p.create_analytics = '1' ) JOIN at.account ac  GROUP BY at.property, at.value, ac.service

但不知何故，我的语法不起作用.错误消息显示如下:

But somehow, my syntax isn't working. A error message is showing as below:

语义错误] '( SELECT at FROM' 附近的第 0 行第 29 行:错误:类 '(' 未定义.

Semantical Error] line 0, col 29 near '( SELECT at FROM': Error: Class '(' is not defined.

在其他任何地方也没有找到类似的例子.有没有人可以帮助修复此 DQL 查询以使其正常工作?提前致谢.

Didn't find similar example anywhere else as well. Does anyone can help to fix this DQL query to get working? Thanks in advance.

推荐答案

使用 createSubquery() 函数在 Doctrine 中创建子查询.然后，您可以将子查询嵌套到主查询中.

Use the createSubquery() function to create a subquery in Doctrine. You can then nest the subquery into your main query.

// build root query
$query = Doctrine_Query::create()
  ->from('Movie m')
  ->where('name = ?', 'Prometheus')
;

// build subquery
$subquery = $query->createSubquery()
  ->from('SeenMovie sm')
  ->where('m.name = sm.name')
;

// nest subquery and execute
$query->where('EXISTS (' . $subquery->getDql() . ')')->execute();

进一步阅读
用于创建任何复杂性的 Doctrine 子查询的防弹模式

这篇关于DQL 中嵌套子查询中的错误:未定义类“("的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！