mysql - 具有 SUM 中条件的 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();

Doctrine 是否支持带有条件的聚合函数 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

在 Doctrine 中，您不需要使用 case when 作为条件 sum

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