Doctrine 2 DQL CASE WHEN in Count

2022-01-03 00:00:00 mysql symfony doctrine-orm dql

我在本机 MySQL 代码中有这个查询

I have this Query in native MySQL Code

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;

我需要在 Doctrine 2 DQL 中重建它

I need to rebuild this in Doctrine 2 DQL

到目前为止我的尝试是这样的:

My attempt so far is this:

SELECT t, COUNT((CASE WHEN BundleEntityPoi p.image = 1 then 1 ELSE NULL END)) AS num
FROM BundleEntityTurn t
JOIN t.pois p
GROUP BY t.id
ORDER BY num DESC

我收到此错误:

在 Bundle:Admin:showTurnsFiltered.html 中渲染模板时抛出异常([语法错误] line 0, col 99: Error: Expected end of string, got '.'").twig 在第 75 行.

我做错了什么?

推荐答案

经过数小时的尝试和搜索，我自己找到了它，它与此 DQL 配合使用:

I found it by myself after hours of trying and searching, it's working with this DQL:

$dql = 'SELECT t, SUM(CASE WHEN p.image = 1 THEN 1 ELSE 0 END) AS numImage
                    FROM BundleEntityTurn t
                    JOIN t.pois p
                    GROUP BY t.id
                    ORDER BY numImage DESC;

重要的是您需要使用 SUM 而不是 COUNT

Important that you need to use SUM instead of COUNT

相关文章