Subquery with queryBuilder Symfony

Question

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?

[**Please Never** post images of or off site links to code, data or error messages](https://meta.stackoverflow.com/a/285557/2310830). Please edit your question and include copy/paste the text into the question, formatted. This is so that we can try to reproduce the problem without having to re-type everything, and your question can be properly indexed or read by screen readers. — RiggsFolly, Sep 15 '22 at 12:32
The trick is to use two $qb instances. One for the subquery and one for the main query. Use $subQueryBuilder->toSql() to feed the subquery into your main query. — Cerad, Sep 15 '22 at 14:45
Thank you Cerad, i'll try this and i'll let you know if it works — Shensil, Sep 15 '22 at 15:22

score 0 · Answer 1 · edited Sep 20 '22 at 12:42

0

You need to use $expr = $em->getExpressionBuilder();.

You can check this question and use it for your code Doing a WHERE .. IN subquery in Doctrine 2

edited Sep 20 '22 at 12:42

Abdulla Nilam

36,589
17
64
85

answered Sep 15 '22 at 13:21

Glancu

54
4

Link only answers tend to be frowned upon on stackoverflow and often lead to downvotes. If you really want to earn some rep then I'd suggest posting a working tested solution. And be aware that you seldom need to use $expr in Doctrine even though they feature heavily in the docs. Most of the time a simple `=` works fine and is easier to read. So the first part of your `answer` in which you claim that $expr is required is just plain wrong. – Cerad Sep 15 '22 at 14:38

Subquery with queryBuilder Symfony

1 Answers1