MySQL Doctrine 2 DQL CASE WHEN in Count
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24568898/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Doctrine 2 DQL CASE WHEN in Count
提问by KhorneHoly
I have this Query in native MySQL Code
我在本机 MySQL 代码中有这个查询
SELECT *
FROM `turn`
LEFT JOIN (
poi
) ON ( turn.id = poi.turn_id )
GROUP BY turn.id
ORDER BY count( case when poi.image = 1 then 1 else null end) DESC;
I need to rebuild this in Doctrine 2 DQL
我需要在 Doctrine 2 DQL 中重建它
My attempt so far is this:
到目前为止，我的尝试是这样的：
SELECT t, COUNT((CASE WHEN Bundle\Entity\Poi p.image = 1 then 1 ELSE NULL END)) AS num
FROM Bundle\Entity\Turn t
JOIN t.pois p
GROUP BY t.id
ORDER BY num DESC
And im getting this error:
我收到这个错误：
An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 99: Error: Expected end of string, got '.'") in Bundle:Admin:showTurnsFiltered.html.twig at line 75.
An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 99: Error: Expected end of string, got '.'") in Bundle:Admin:showTurnsFiltered.html.twig at line 75.
What am i doing wrong?
我究竟做错了什么？
回答by KhorneHoly
I found it by myself after hours of trying and searching, it's working with this DQL:
经过数小时的尝试和搜索，我自己找到了它，它正在使用此 DQL：
$dql = 'SELECT t, SUM(CASE WHEN p.image = 1 THEN 1 ELSE 0 END) AS numImage
FROM Bundle\Entity\Turn t
JOIN t.pois p
GROUP BY t.id
ORDER BY numImage DESC;
Important that you need to use SUM instead of COUNT
重要的是您需要使用 SUM 而不是 COUNT
回答by Rybus
You need to use ResultSetMappingBuilder. It would look something like :
您需要使用ResultSetMappingBuilder。它看起来像：
public function getTurn()
{
$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addRootEntityFromClassMetadata('Foo\BarBundle\Entity\Turn', 't');
$rsm->addJoinedEntityFromClassMetadata('Foo\BarBundle\Entity\Poi', 'p', 't', 'poi', array('id' => 'poi_id'));
$rsm->addScalarResult('ImageCount', 'ImageCount');
$sql = 'SELECT t.id, t.foo, t.bar,
SUM(CASE WHEN p.image = 1 then 1 else null end) ImageCount,
FROM Turn t
INNER JOIN poi p ON t.id = p.turn_id
ORDER BY ImageCount DESC';
$query = $this->_em->createNativeQuery($sql, $rsm);
return $query->getScalarResult();
}
note:you might need to change $query->getScalarResult()to $query->getResult().
注意：您可能需要更改$query->getScalarResult()为$query->getResult().
