原则2 DQL案例在计数 [英] Doctrine 2 DQL CASE WHEN in Count

问题描述

  SELECT * 
 FROM`turn` 
 LEFT JOIN（
 poi 
）ON（turn.id = poi.turn_id）
 GROUP BY turn.id 
 ORDER BY count（当poi.image = 1时的情况1否则为null结束）DESC; 
  

我需要在Doctrine 2 DQL中重建这个DQL

目前为止，我的尝试是这样的：

  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 
  

我收到此错误：

在渲染模板期间抛出异常（[Syntax Error] line 0，col 99：Error：Expected end of string，got'。'）在Bundle：Admin中：showTurnsFiltered .html.twig在第75行。

我做错了什么？

解决方案

在尝试和搜索几个小时后，我发现自己正在使用这个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; 
  

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

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;

I need to rebuild this in 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.

What am i doing wrong?

解决方案

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 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

这篇关于原则2 DQL案例在计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！