条件和的DQL

Question

我有一个这样的DQL：

$dql = "SELECT d.name, "
. "SUM(m.id and m.status = 'a') members, "
. "SUM(m.position_id = 19 and m.status = 'a') subcon, "
. "SUM(m.position_id = 20 and m.status = 'a') ojt, "
. "SUM(m.status = 'h') hr_intra "
. "FROM OssOrgBundle:User m "
. "RIGHT JOIN OssOrgBundle:Department d "
. "ON m.department = d.id "
. "GROUP BY d.id";

$qb = $em->createQuery($dql);
deptListCount = $qb->getResult();

理论是否支持内部有条件的聚合函数SUM？我在SQL中尝试了这个查询，它工作了。我在将它转换为DQL时遇到了问题

我还使用以下命令完成了此操作：

$deptListCount = $em->createQueryBuilder("r")
->select("d.name")
->addSelect("SUM(m.id AND m.status = 'a') members")
->addSelect("SUM(m.position_id = 19 and m.status = 'a') subcon")
->addSelect("SUM(m.position_id = 20 and m.status = 'a') ojt")
->addSelect("SUM(m.status = 'h') hr_intra ")
->from("OssOrgBundle:Department", "d")
->leftJoin("OssOrgBundle:User", "m", "WITH", "m.deprtment = :id")
->groupBy("d.id")
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);

还是不能工作

Answer 1

不，在理论中，您需要为条件sum使用case when

sum( case when m.position_id = 19 and m.status = 'a' then 1 else 0 end ) as subcon

更改DQL中的其余部分，还

m.id AND m.status = 'a'不确定您尝试应用的是什么逻辑，我假设您正在寻找如下内容

sum( case when m.id is not null and m.status = 'a' then 1 else 0 end ) as members