Symfony QueryBuilder使用sprintf编写子查询的实现问题

我正在实现一个复杂度较高的QueryBuilder查询，对应的原生SQL语句如下：

SELECT DISTINCT fif.idfichefrais, fif.idvisiteur, fif.date, fif.nbjustificatifs, fif.montantvalide, fif.datemodif, fif.idetat, montantTotalFraisForfait, montantTotalFraisHorsForfait, libelleFraisHorsForfait
FROM FicheFrais fif
INNER JOIN (SELECT lff.idfichefrais, SUM(frf.montantFraisForfait*lff.quantite) as montantTotalFraisForfait
            FROM LigneFraisForfait AS lff
            INNER JOIN FraisForfait AS frf ON frf.idfraisforfait = lff.idfraisforfait
            GROUP BY lff.idfichefrais) a USING (idfichefrais)
INNER JOIN (SELECT e.idetat, e.libelleEtat
            FROM Etat AS e) b USING (idetat)
LEFT JOIN (SELECT lfhf.idfichefrais, SUM(lfhf.montant) as montantTotalFraisHorsForfait, lfhf.libelle as libelleFraisHorsForfait
            FROM LigneFraisHorsForfait AS lfhf
            GROUP BY lfhf.idfichefrais) c USING (idfichefrais)
WHERE fif.idvisiteur = '?' 
AND YEAR(fif.date) ='?'
ORDER BY MONTH(fif.date) ASC;

目前正在编写的QueryBuilder代码如下：

public function findFicheFraisByIdVisiteurAndAnnee($idvisiteur, $annee): array
    {
        $qb = $this->getEntityManager()->createQueryBuilder();
        $a = $this->getEntityManager()->createQueryBuilder();
        $b = $this->getEntityManager()->createQueryBuilder();
        $c = $this->getEntityManager()->createQueryBuilder();

        $selectFraisForfait = $a->select(array('lff.idfichefrais, SUM(frf.montantFraisForfait*lff.quantite)'))
                            ->from('App:Lignefraisforfait', 'lff')
                            ->innerJoin('App:Fraisforfait', 'frf', Join::WITH, 'frf.idfraisforfait = lff.idfraisforfait')
                            ->groupBy('lff.idfichefrais');

        $selectEtat = $b->select(array('e.idetat, e.libelleEtat ad libelleFraisHorsForfait'))
                            ->from('App:Etat', 'e');
        
        $selectFraisHorsForfait = $c->select(array('lfhf.idfichefrais, SUM(lfhf.montant) as montantTotalFraisHorsForfait'))
                            ->from('App:Lignefraishorsforfait','lfhf')
                            ->groupBy('lfhf.idfichefrais');

        return $FicheFraisByIdVisiteurAndAnnee = $qb
            ->select(array('fif.idfichefrais, identity(fif.idvisiteur) as idvisiteur, fif.date, fif.nbjustificatifs, fif.montantvalide, fif.datemodif, identity(fif.idetat) as idetat,montantTotalFraisForfait, montantTotalFraisHorsForfait, libelleFraisHorsForfait'))
            ->from('App:Fichefrais', 'fif')
            ->innerJoin('App:Lignefraisforfait', sprintf('($s)selectfraisforfait', $selectFraisForfait->getDQL()), Join::WITH, 'fif.idfichefrais = selectfraisforfait.idfichesfrais')
            ->innerJoin('App:Etat', sprintf('($s)selectetat', $selectEtat->getDQL()), Join::WITH, 'fif.idetat = selectetat.idetat')
            ->leftJoin('App:Lignefraishorsforfait', sprintf('($s)selectFraisHorsForfait', $selectFraisHorsForfait->getDQL()), Join::WITH, 'fif.idfichefrais = selectfraishorsforfait.idfichefrais')
            ->where('fif.idvisiteur = :idvisiteur')
            ->setParameter('idvisiteur' , $idvisiteur)
            ->andWhere('YEAR(fif.date) = :annee')
            ->setParameter('annee', $annee)
            ->groupBy('fif.idfichefrais')
            ->groupBy('lfhf.idfichefrais')
            ->orderBy('MONTH(fif.date)', 'ASC')
            ->setMaxResults(12)
            ->getQuery()
            ->getResult()

        ;
    }

问题修正说明 #

现有代码存在以下问题：

• sprintf格式化占位符错误，使用了($s)应该改为%s
• 子查询关联语法错误，Doctrine JOIN子查询不需要写原实体名，子查询DQL需要用括号包裹
• 子查询字段别名错误，Etat子查询把as错写为ad，且别名和非包干费用字段重名
• 包干费用子查询的求和结果没有设置montantTotalFraisForfait别名，非包干费用子查询漏查了libelle字段
• 关联条件字段名写错，把idfichefrais错写为idfichesfrais
• 多余的groupBy配置，且未添加DISTINCT配置

修正后代码 #

use Doctrine\ORM\Query\Expr\Join;

public function findFicheFraisByIdVisiteurAndAnnee($idvisiteur, $annee): array
{
    $em = $this->getEntityManager();
    $qb = $em->createQueryBuilder();
    $subFraisForfait = $em->createQueryBuilder();
    $subEtat = $em->createQueryBuilder();
    $subFraisHorsForfait = $em->createQueryBuilder();

    // 包干费用统计子查询
    $subFraisForfait->select('lff.idfichefrais, SUM(frf.montantFraisForfait * lff.quantite) as montantTotalFraisForfait')
        ->from('App:Lignefraisforfait', 'lff')
        ->innerJoin('App:Fraisforfait', 'frf', Join::WITH, 'frf.idfraisforfait = lff.idfraisforfait')
        ->groupBy('lff.idfichefrais');

    // 状态子查询
    $subEtat->select('e.idetat, e.libelleEtat')
        ->from('App:Etat', 'e');

    // 非包干费用统计子查询
    $subFraisHorsForfait->select('lfhf.idfichefrais, SUM(lfhf.montant) as montantTotalFraisHorsForfait, lfhf.libelle as libelleFraisHorsForfait')
        ->from('App:Lignefraishorsforfait', 'lfhf')
        ->groupBy('lfhf.idfichefrais');

    // 主查询
    return $qb->select('DISTINCT fif.idfichefrais, identity(fif.idvisiteur) as idvisiteur, fif.date, fif.nbjustificatifs, fif.montantvalide, fif.datemodif, identity(fif.idetat) as idetat, a.montantTotalFraisForfait, c.montantTotalFraisHorsForfait, c.libelleFraisHorsForfait')
        ->from('App:Fichefrais', 'fif')
        ->innerJoin(
            sprintf('(%s)', $subFraisForfait->getDQL()),
            'a',
            Join::WITH,
            'fif.idfichefrais = a.idfichefrais'
        )
        ->innerJoin(
            sprintf('(%s)', $subEtat->getDQL()),
            'b',
            Join::WITH,
            'fif.idetat = b.idetat'
        )
        ->leftJoin(
            sprintf('(%s)', $subFraisHorsForfait->getDQL()),
            'c',
            Join::WITH,
            'fif.idfichefrais = c.idfichefrais'
        )
        ->where('fif.idvisiteur = :idvisiteur')
        ->setParameter('idvisiteur', $idvisiteur)
        ->andWhere('YEAR(fif.date) = :annee')
        ->setParameter('annee', $annee)
        ->groupBy('fif.idfichefrais')
        ->orderBy('MONTH(fif.date)', 'ASC')
        ->setMaxResults(12)
        ->getQuery()
        ->getResult();
}

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