Symfony QueryBuilder中CASE语句引发语法错误的解决咨询

原SQL语句可正常执行：

SELECT 
    f.idFile,
    f.FileName,
    f.date_file,
    COUNT(DISTINCT (ligne.numLigne)) AS nbCmde,
    COUNT(DISTINCT CASE WHEN bl.date_production != "0000-00-00 00:00:00" THEN ligne.numLigne END) AS cmdProd,
    COUNT(DISTINCT CASE WHEN bl.date_production = "0000-00-00 00:00:00" THEN ligne.numLigne END) AS cmdNonProd,
    COUNT(DISTINCT CASE WHEN tr.`idStatut` = 10 THEN ligne.numLigne END) AS rupture,
    COUNT(DISTINCT CASE WHEN tr.`flag_xport` = TRUE THEN ligne.numLigne END) AS retention,  
    MIN(bl.date_production)AS min_date_production,
    MAX(bl.date_production)  AS max_date_production,
    SUM(cmd.quantite)  AS nbArticles, 
    tr.date_depot
FROM ecomm_lignes AS ligne
INNER JOIN `ecomm_files` AS f ON f.idFile = ligne.idFile
LEFT JOIN ecomm_bl AS bl ON ligne.numBL = bl.BL
INNER JOIN ecomm_tracking AS tr ON ligne.numLigne = tr.numLigne
LEFT JOIN ecomm_cmdep AS cmd ON ligne.numBL = cmd.numBL
INNER JOIN ecomm_appli AS appli ON f.idAppli = appli.idAppli
WHERE appli.idAppli = 774 
AND f.date_file BETWEEN "2023-06-17 00:00:00" AND "2023-07-17 00:00:00"
GROUP BY f.idfile
ORDER BY f.date_file DESC

但转换为Symfony QueryBuilder查询时出现语法错误：

public function findBlByOpeByFileByDate($idAppli,$dateDebut, $dateFin)  {

    return $this->createQueryBuilder('ligne')
                ->leftJoin('ligne.ecommCmdeps', 'cmd')
                ->innerJoin('ligne.numligne', 'tr')
                ->innerJoin('tr.idfile', 'file')
                ->innerJoin('file.idappli', 'app')
                ->innerJoin('tr.idStatut','statut')
                ->leftJoin('ligne.bls','bl')
                ->where('app.idappli IN (:id)')
                ->setParameter('id', $idAppli)
                ->andWhere('file.dateFile >= :dateDebut')
                ->setParameter('dateDebut', $dateDebut)
                ->andWhere('file.dateFile <= :dateFin')
                ->setParameter('dateFin', $dateFin)
                ->select('file.idfile')
                ->addSelect('file.dateFile')
                ->addSelect('file.filename')
                ->addSelect('COUNT(DISTINCT ligne.numligne) as nbCmde')
                ->addSelect('COUNT(DISTINCT CASE WHEN bl.siteProduction != 0 THEN ligne.numligne END) as cmdProd')
                ->addSelect('MIN(bl.dateProduction) as minDateProd')
                ->addSelect('MAX(bl.dateProduction) as maxDateProd')
                ->addSelect('SUM(cmd.quantite) as nbArticles')
                ->addSelect('tr.dateDepot')
                ->groupBy('file.idfile')
                ->orderBy('file.dateFile')
                ->getQuery()
                ->getArrayResult();
}

错误信息：

[Syntax Error] line 0, col 245: Error: Unexpected ')'

排查后确认错误来自包含CASE的语句行，删除该行后错误消失，请问如何正确编写该查询？

解决方案 #

问题出在Doctrine QueryBuilder对CASE语句的语法要求上：原生SQL可省略的ELSE分支，在QueryBuilder中必须明确写为ELSE NULL，否则Doctrine的SQL解析器会抛出语法错误。

同时需要修正两个细节：

1. 原SQL判断的是bl.date_production字段，QueryBuilder中误写为bl.siteProduction，需对齐字段名避免逻辑偏差；
2. tr.dateDepot不属于分组键，直接查询会触发非聚合字段的分组问题，需用聚合函数（如MAX()）包裹。

修正后的QueryBuilder代码如下：

public function findBlByOpeByFileByDate($idAppli,$dateDebut, $dateFin)  {

    return $this->createQueryBuilder('ligne')
                ->leftJoin('ligne.ecommCmdeps', 'cmd')
                ->innerJoin('ligne.numligne', 'tr')
                ->innerJoin('tr.idfile', 'file')
                ->innerJoin('file.idappli', 'app')
                ->innerJoin('tr.idStatut','statut')
                ->leftJoin('ligne.bls','bl')
                ->where('app.idappli IN (:id)')
                ->setParameter('id', $idAppli)
                ->andWhere('file.dateFile >= :dateDebut')
                ->setParameter('dateDebut', $dateDebut)
                ->andWhere('file.dateFile <= :dateFin')
                ->setParameter('dateFin', $dateFin)
                ->select('file.idfile')
                ->addSelect('file.dateFile')
                ->addSelect('file.filename')
                ->addSelect('COUNT(DISTINCT ligne.numligne) as nbCmde')
                // 修正CASE语句，添加ELSE NULL分支并对齐原SQL字段
                ->addSelect('COUNT(DISTINCT CASE WHEN bl.dateProduction != :emptyDate THEN ligne.numligne ELSE NULL END) as cmdProd')
                ->setParameter('emptyDate', '0000-00-00 00:00:00')
                ->addSelect('MIN(bl.dateProduction) as minDateProd')
                ->addSelect('MAX(bl.dateProduction) as maxDateProd')
                ->addSelect('SUM(cmd.quantite) as nbArticles')
                // 用聚合函数处理非分组字段
                ->addSelect('MAX(tr.dateDepot) as dateDepot')
                ->groupBy('file.idfile')
                ->orderBy('file.dateFile', 'DESC')
                ->getQuery()
                ->getArrayResult();
}

如果需要还原原SQL中的其他CASE统计项（如cmdNonProd、rupture、retention），同样遵循CASE ... ELSE NULL END的写法即可，示例：

->addSelect('COUNT(DISTINCT CASE WHEN bl.dateProduction = :emptyDate THEN ligne.numligne ELSE NULL END) as cmdNonProd')
->addSelect('COUNT(DISTINCT CASE WHEN tr.idStatut = 10 THEN ligne.numligne ELSE NULL END) as rupture')
->addSelect('COUNT(DISTINCT CASE WHEN tr.flagXport = TRUE THEN ligne.numligne ELSE NULL END) as retention')

内容的提问来源于stack exchange，提问作者jjnadoux45