使用 SUM(a.id=1) 作为"识别器"时的学说错误：预期的学说\ORM\查询\Lexer：：T_CLOSE_PARENTHESIS，得到'='

Question

我正试图在条令中执行一个包含类似的查询

SUM(a.id = 1) as `1`

由于某些原因，它总是给我以下错误：

[Syntax Error] line 0, col 15: Error: Expected DoctrineORMQueryLexer::T_CLOSE_PARENTHESIS, got '='

这是我正在使用的代码

$result = $em->getRepository('MyBundle:PlayerAction')
->createQueryBuilder('pa')
->select(array(
'SUM(a.id=1) as `1`,
SUM(a.id=2) as `2`,
SUM(a.id=3) as `3`,
p.playerName,
pa.timestamp'
))
->innerJoin('pa.action', 'a')
->innerJoin('pa.player', 'p')
->where('pa.timestamp > ?1')
->groupBy('p')
->setParameter(1, time() - $time)
->orderBy('p.playerName', 'ASC');

Answer 1

考虑到错误消息，Doctrine似乎对您的MySQL表达式进行了一些自己的解析。正如nietonfir已经提出的，这个解析器可能不完全符合MySQL。你可以尝试一些语法变体，例如：

SUM(CASE a.id WHEN 1 THEN 1 ELSE 0 END) as `1`,
SUM(CASE a.id WHEN 2 THEN 1 ELSE 0 END) as `2`,
SUM(CASE a.id WHEN 3 THEN 1 ELSE 0 END) as `3`

或：

SUM(IF(a.id=1, 1, 0)) as `1`,
SUM(IF(a.id=2, 1, 0)) as `2`,
SUM(IF(a.id=3, 1, 0)) as `3`

如果失败，则重构查询；沿着这条线(只是在这里大声思考；我将把QueryBuilder等效程序留给你)：

SELECT
(SELECT COUNT(*) FROM PlayerAction AS pa WHERE pa.player = p.id AND pa.timestamp > ?1 AND pa.action = 1) AS `1`,
(SELECT COUNT(*) FROM PlayerAction AS pa WHERE pa.player = p.id AND pa.timestamp > ?1 AND pa.action = 2) AS `2`,
(SELECT COUNT(*) FROM PlayerAction AS pa WHERE pa.player = p.id AND pa.timestamp > ?1 AND pa.action = 3) AS `3`,
p.playerName
FROM Player p
ORDER BY p.playerName ASC

这可能会对查询性能产生负面影响。

Answer 2

条令似乎不支持这种语法。您可以重写查询(会想到子查询或一些联接)，也可以使用本地MySQL查询。