如何使用Symfony QueryBuilder转换含子查询的SQL语句？

问题背景 #

需要将一段原生SQL转换为Symfony QueryBuilder写法，处理子查询时遇到困难。原生SQL的逻辑是：先获取每个Router_name对应的最大版本号，再统计符合指定cuid和etat条件的最新版本记录数。

原始原生SQL的PHP实现 #

function stat_etat($cuid, $db, $etat)
{
    $requete = $db->prepare('SELECT count(*)
    FROM fiches_de_production table1,
      (SELECT MAX(Version) AS maxversion, Router_name
      FROM fiches_de_production
      GROUP BY Router_name) table2
    WHERE (table1.LDM_LDM_LDM = ? AND table1.Etat = ?
    AND table1.Router_name = table2.Router_name
    AND table1.version= table2.maxversion)'); 
    $requete->execute(array($cuid,$etat));
    $res = $requete->fetch();
    $requete->closeCursor();
    return $res['count(*)']; 
}

尝试的QueryBuilder代码（存在问题） #

public function stat_etat(String $cuid, $etat)
{
    //$scalar = $query->getScalarResult();
    $entityManager = $this->getEntityManager();
    $query = $entityManager -> createQueryBuilder()
        ->select('count(f)')
        ->from('App\Entity\FichesDeProduction', 'f')
            ->addSelect('(SELECT MAX(f.version) AS maxversion
                    FROM App\Entity\FichesDeProduction f
                    GROUP BY f.Router_name) AS table2')
        ->where('f.LDM_LDM_LDM = :cuid')
        ->andWhere ('f.etat = :etat')
        ->andWhere ('f.Router_name = table2.Router_name')
        ->andWhere ('f.version = table2.maxversion')
        ->setParameter('cuid', $cuid)
        ->setParameter('etat', $etat)
        ->getQuery()
        ->getResult();
}

正确实现方式 #

方式一：将子查询作为关联表JOIN #

这种方式和原生SQL逻辑完全对应，先构建子查询获取每个Router_name的最大版本，再将其作为临时表与主表关联：

public function stat_etat(string $cuid, $etat)
{
    $entityManager = $this->getEntityManager();
    
    // 构建子查询：获取每个Router_name对应的最大版本
    $subQueryBuilder = $entityManager->createQueryBuilder()
        ->select('f_sub.Router_name, MAX(f_sub.version) AS maxversion')
        ->from('App\Entity\FichesDeProduction', 'f_sub')
        ->groupBy('f_sub.Router_name');
    
    // 主查询：关联子查询结果，统计符合条件的记录数
    $queryBuilder = $entityManager->createQueryBuilder()
        ->select('COUNT(f.id)') // 使用实体主键统计更高效
        ->from('App\Entity\FichesDeProduction', 'f')
        // 关联子查询生成的临时表
        ->join(
            sprintf('(%s)', $subQueryBuilder->getDQL()),
            't2',
            'WITH',
            'f.Router_name = t2.Router_name AND f.version = t2.maxversion'
        )
        ->where('f.LDM_LDM_LDM = :cuid')
        ->andWhere('f.etat = :etat')
        ->setParameter('cuid', $cuid)
        ->setParameter('etat', $etat);
    
    // 获取单一标量结果并转为整数返回
    return (int) $queryBuilder->getQuery()->getSingleScalarResult();
}

方式二：使用WHERE EXISTS子查询 #

这种方式通过EXISTS判断当前记录是否为对应Router_name的最新版本，逻辑更简洁：

public function stat_etat(string $cuid, $etat)
{
    $entityManager = $this->getEntityManager();
    
    // 构建EXISTS子查询：判断当前记录是否是对应Router_name的最大版本
    $existsSubQuery = $entityManager->createQueryBuilder()
        ->select('1')
        ->from('App\Entity\FichesDeProduction', 'f_sub')
        ->where('f_sub.Router_name = f.Router_name')
        ->groupBy('f_sub.Router_name')
        ->having('MAX(f_sub.version) = f.version');
    
    $queryBuilder = $entityManager->createQueryBuilder()
        ->select('COUNT(f.id)')
        ->from('App\Entity\FichesDeProduction', 'f')
        ->where('f.LDM_LDM_LDM = :cuid')
        ->andWhere('f.etat = :etat')
        ->andWhere($existsSubQuery->getDQL())
        ->setParameter('cuid', $cuid)
        ->setParameter('etat', $etat);
    
    return (int) $queryBuilder->getQuery()->getSingleScalarResult();
}

问题说明 #

你之前的写法错误在于用addSelect将子查询作为字段添加，这会导致子查询结果被当作一列值，而非可关联的临时表，因此无法在WHERE条件中引用table2.Router_name这类字段。正确的做法是将子查询作为JOIN的表，或者用EXISTS子查询来实现逻辑。

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