如何从Doctrine和Symfony获得汇总标量结果？

Question

How do I get Person, Application, Scalar A as a result set?

I am currently getting an array with [0] => Person [1] => Application etc. The 'addScalarResult()' call seems to be getting ignored.

public function exportHR63Status(Semester $semester) {
  $resultSetMapping = new ResultSetMappingBuilder($this->getEntityManager());
  $resultSetMapping->addRootEntityFromClassMetadata('Unsw\CamsBundle\Entity\Person', 'p');
  $resultSetMapping->addRootEntityFromClassMetadata('Unsw\CamsBundle\Entity\Application', 'a',
  array(
    'id' => 'application_id',
    'deleted' => 'application_deleted',
    'created' => 'application_created',
    'updated' => 'application_updated'
  ));

  $selectClause = $resultSetMapping->generateSelectClause();

  $resultSetMapping->addScalarResult('signedCasual', 'signedCasual');
  $resultSetMapping->addScalarResult('signedFinance', 'signedFinance');
  $resultSetMapping->addScalarResult('signedHeadOfSchool', 'signedHeadOfSchool');

  $q = $this->getEntityManager()->createNativeQuery("SELECT " . $selectClause . ",
    EXISTS (select 1 from hr63signer_hr63submission hshs INNER JOIN hr63signer hs ON hshs.hr63signer_id = hs.id WHERE h1_.id = hshs.hr63submission_id and hs.signertype = 'casual') as signedCasual,
    EXISTS (select 1 from hr63signer_hr63submission hshs INNER JOIN hr63signer hs ON hshs.hr63signer_id = hs.id WHERE h1_.id = hshs.hr63submission_id and hs.signertype = 'finance') as signedFinance,
    EXISTS (select 1 from hr63signer_hr63submission hshs INNER JOIN hr63signer hs ON hshs.hr63signer_id = hs.id WHERE h1_.id = hshs.hr63submission_id and hs.signertype = 'headofschool') as signedHeadOfSchool
    -- processed finance is hr63_submission.processed_finance
    FROM hr63_submission h1_
    INNER JOIN course c2_ ON h1_.course_id = c2_.id
    INNER JOIN application_courses ac ON c2_.id = ac.course_id
    INNER JOIN application a ON ac.application_id = a.id
    INNER JOIN person p ON h1_.person_id = p.znumber
    WHERE c2_.semester_id = 14 -- either course or application should give the same result", $resultSetMapping);

  $q->setParameter('semester_id', $semester->getId());

  return $q->getResult();
}

Symfony 2.7.6, Doctrine 2.5.1

Answer 1

The answer is that I had the parameter for semester_id hard coded to 14 and bound a parameter that did not match. Change

c2_.semester_id = 14

to

c2_.semester_id = :semester_id

Gives:

[0] Person
[1] Application plus scalar entries.