PHP Doctrine - Annidate select in a Join, how do join with other query

Question

I'm new on PHP Doctrine and I have some problems! I have this query in the Processor

SELECT h2.Date AS CalDate, h2.Manager_Id, h2.ProjectCode, h2.Project, h2.Name, h2.CN, h2.Description, h2.HourType, h2.Hours AS HoursAdj, h2.Correction 
    FROM Calendar cal2 JOIN
      (SELECT cn2.CN_id, cn2.CN, cn2.Description, p2.Name, hr2.Hours,  hr2.Correction, hr2.Date, ht2.Name AS HourType, prj2.Manager_Id, prj2.Name AS Project, prj2.ProjectCode 
    FROM Hours hr2
      RIGHT JOIN CN cn2 ON hr2.CN_id = cn2.CN_id 
      RIGHT JOIN Persons p2 ON cn2.Persons_id = p2.Persons_id
      JOIN HourType ht2 ON hr2.HourType_Id = ht2.HourType_Id
      JOIN Projects prj2 ON hr2.Projects_Id = prj2.Projects_Id) h2 ON cal2.date = h2.Date
      WHERE h2.Date BETWEEN '$dateFrom' AND '$dateTo' AND Correction = 1

if ($project !== null && $project !== "ALL")
  $query .= " AND h2.ProjectCode = '$project'";
 else if ($pm !== null && $pm !== "00000")
  $query .= " AND h2.Manager_Id = '$pm'";
 if ($consultant !== null && $consultant !== "ALL")
  $query .= " AND h2.CN_id = '$consultant'";

I have tried to resolve this separating the 2 query. The internal one:

$qb_int_1 = $this->defaultEntityManager->createQueryBuilder();
            $qb_int_1 ->select('cn2.cnId', 'cn2.cn', 'cn2.description', 'p2.name', 'hr2.hours', 'hr2.correction', 'hr2.date', 'ht2.name AS hourType', 'prj2.managerId', 'prj2.name AS projectName', 'prj2.projectcode')
                    ->from('TimereportdbBundle:Persons', 'p2')
                    ->leftJoin('TimereportdbBundle:Cn', 'cn2', 'WITH', 'cn2.personsId = p2.personsId')
                    ->leftJoin('TimereportdbBundle:Hours', 'hr2', 'WITH', 'hr2.cnId = cn2.cnId')
                    ->join('TimereportdbBundle:HourType', 'ht2', 'WITH', 'hr2.hourtypeId = ht2.hourtypeId')
                    ->join('TimereportdbBundle:Projects', 'prj2', 'WITH', 'hr2.projectsId = prj2.projectsId');

and after, I try to resolve the external in this way:

$qb_adjReport_1 = $this->defaultEntityManager->createQueryBuilder();
            $qb_adjReport_1->select('h2.date AS calDate', 'h2.managerId', 'h2.projectcode', 'h2.project', 'h2.name', 'h2.cn', 'h2.description', 'h2.hourType', 'h2.Hours AS hoursAdj', 'h2.correction')
               ->from('TimereportdbBundle:Calendar', 'cal2')
               ->join($qb_int_1, 'h2', 'WITH', 'cal2.date', 'h2.Date')
               ->where('h2.date BETWEEN :dateFrom AND :dateTo')
                    ->andwhere('h2.correction = 1')
               ->setParameter('dateFrom', $dateFrom)
               ->setParameter('dateTo', $dateTo);     
            if ($projectcode !== null && $projectcode !== "ALL"){
                $qb_adjReport_1->andwhere('h2.projectcode = :projectcode')
                ->setParameter('projectcode', $projectcode);
            }
            else if ($pm !== null && $pm !== "00000") {
                    $qb_adjReport_1->andwhere('h2.managerId = :managerId')
                ->setParameter('managerId', $pmId);
                }
            if($consultant !== null && $consultant !== "ALL"){
                $qb_adjReport_1->andwhere('h2.cn = :consultant')
                ->setParameter('consultant', $consultant);
            }
            
            $adjReports1 = $qb_adjReport_1->getQuery()->getResult();

I've tried to resolve the query in this way looking this 2 links enter link description here

enter link description here

This is the error that receive on the last getResult() instruction: Doctrine\ORM\Query\QueryException: [Semantical Error] line 0, col 199 near 'SELECT cn2.cnId,': Error: Class 'SELECT' is not defined.

Thanks in advance.

PHP Doctrine - Annidate select in a Join, how do join with other query

0 Answers0