我试图在包含这样的东西的学说中执行查询
SUM(a.id = 1) as `1`
由于某些原因,它总是给我以下错误:
[Syntax Error] line 0, col 15: Error: Expected Doctrine\ORM\Query\Lexer::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');
Ruud Helderm.. 5
考虑到错误消息,似乎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
这可能会对查询性能产生负面影响.
考虑到错误消息,似乎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
这可能会对查询性能产生负面影响.