Subquery with queryBuilder Symfony

Viewed 38

I'm trying to convert an SQL command to querybuilder with symfony but I'm having trouble with a subquery (I've already tried in DQL but its seems its not possible to include subqueries)

Here the initial function with 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(*)']; 
  }

And here is the code with symfony im trying to do with 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();
    

How would I do this query with the query builder?

1 Answers
Related