Convert your own SQL query to the Symfony2 query builder (with a choice of join)

Question

I have this (huge) sql query to convert to doctrine2.

i already succeeded with createNativeQuery(), but sometimes the results do not match the original (from mysql)

here is the request:

 SELECT e1_.etapes AS etapes3, e1_.position AS position4, c0_.exercicesData_id AS Exercice_id, exo.titre as Exo, age.bornInf as ageInf, age.bornSup as ageSup, imc.bornInf as imcInf, imc.bornSup as imcSup, sexe.bornInf as sexeInf, sexe.bornSup as sexeSup

 FROM ContrainteData c0_ 

 LEFT JOIN ExerciceData e1_ ON c0_.exercicesData_id = e1_.id 
 LEFT JOIN Exercice exo ON e1_.exercice_id = exo.id 
 LEFT JOIN contraintedata_entrydata c3_ ON c0_.id = c3_.contraintedata_id 
 INNER JOIN (SELECT * FROM `entrydata` GROUP BY type_id, bornInf, bornSup) AS age ON   age.type_id = 1
 INNER JOIN (SELECT * FROM `entrydata` GROUP BY type_id, bornInf, bornSup) AS imc ON imc.type_id = 4
 INNER JOIN (SELECT * FROM `entrydata` GROUP BY type_id, bornInf, bornSup) AS sexe ON sexe.type_id = 5

 WHERE
      ( age.bornSup >= 30 and age.bornInf <= 30 ) and
      ( imc.bornSup >= 20 and imc.bornInf <= 20 ) and
      ( sexe.bornSup >= 1 and sexe.bornInf <= 1 ) and
      e1_.entrainement_id = 6

 GROUP BY etapes3, position4

 ORDER BY etapes3 ASC, position4 ASC

With queryBuilder, I did the following:

    $qb1            = $this->createQueryBuilder('c');
    $qb2            = $this->createQueryBuilder('t');


    $qb2->select( 't' )
        ->from  ( 'bonk\AppBundle\Entity\EntryData', 'en' )
        ->groupBy( 'en.type')
        ->addGroupBy( 'en.bornInf' )
        ->addGroupBy( 'en.bornSup' );


    $qb1->select( 'c' )
        ->leftJoin( 'c.exercicesData', 'e'  )
        ->leftJoin( 'e.exercice'     , 'ex' )
        ->join    ( $qb2->getDql(), 'age', 'with', 'age.type = 1' )
        ->join    ( $qb2->getQuery()->getDql(), 'imc', 'with', 'imc.type = 4' )
        ->join    ( $qb2->getQuery()->getDql(), 'sexe', 'with', 'sexe.type = 5')
        ->where   ( 'age.bornSup >= 30 and age.bornInf <= 30'                    )
        ->andWhere( 'imc.bornSup >= 20 and imc.bornInf <= 20'                    )
        ->andWhere( 'sexe.bornSup >= 1 and sexe.bornInf <= 1'                    )
        ->andWhere( 'e.entrainement = 6'                                         )
        ->groupBy ( 'e.etapes', 'ASC'                                            )
        ->addGroupBy( 'e.position', 'ASC'                                        );

    return $qb1->getQuery()->getArrayResult();

I have the following error:

      "[Semantical Error] line 0, col 116 near 'SELECT t FROM': Error: Class 'SELECT' is not defined "

Answer 1

i finally get it with the mysql view, which is called a fake object in doctrine and DQL Query.

to create a view in sql just run the following line:

"create view VIEWNAME YOUQUERY"

in my example:

"create view entrydataView SELECT * FROM `entrydata` GROUP BY type_id, bornInf, bornSup"