How to get a collection of related entities by using Doctrine ResultSetMapping?

Question

I use Doctrine 2.3.4. and Symfony 2.3.0

I have two entities: Person and Application.

Application gets created when some Person applies for a job.

Relation from Person to Application is OneToMany, bidirectional.

Using the regular Doctrine documentation here I managed to get a correct result set only when working with a single entity. However, when I add joined entity, I get a collection of root entities but joined to a wrong related entity.

In other words, the problem is that I get a collection of Applications but all having the same Person.

Native sql query, when executed directly returns a correct result.

This is the code:

$sql = "SELECT a.id, a.job, p.first_name, p.last_name 
        FROM application a 
          INNER JOIN person p ON a.person_id = p.id";

    $rsm = new ResultSetMapping;

    $rsm->addEntityResult('\Company\Department\Domain\Model\Application', 'a');
    $rsm->addFieldResult('a','id','id');
    $rsm->addFieldResult('a','job','job');

    $rsm->addJoinedEntityResult('\Company\Department\Domain\Model\Person' , 'p', 'a', 'person');

    $rsm->addFieldResult('p','first_name','firstName');
    $rsm->addFieldResult('p','last_name','lastName');

    $query = $this->em->createNativeQuery($sql, $rsm);

    $result = $query->getResult();

    return $result;

Here are the Entity classes:

namespace Company\Department\Domain\Model;


use Doctrine\ORM\Mapping as ORM;


/**
* Person
*
* @ORM\Entity
* @ORM\Table(name="person")
*/

class Person
{
/**
 * @ORM\Column(type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string First name
 *
 * @ORM\Column(name="first_name",type="string",length=255)
 */
private $firstName;

/**
 * @var string Last name
 *
 * @ORM\Column(name="last_name",type="string",length=255)
 */
private $lastName;

/**
*
* @var Applications[]
* @ORM\OneToMany(targetEntity="Application", mappedBy="person")
*/
private $applications;

Application class:

namespace Company\Department\Domain\Model;


use Doctrine\ORM\Mapping as ORM;



/**
* Application (Person applied for a job)
*
* @ORM\Entity
* @ORM\Table(name="application")
*/

class Application
{

/**
 * @ORM\Column(type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
 private $id;

/**
 * @var Person
 *
 * @ORM\ManyToOne(targetEntity="Person", inversedBy="applications")
 * @ORM\JoinColumn(name="person_id", referencedColumnName="id")
 */
private $person;


/**
 * @var string
 * @ORM\Column(name="job",type="string", length=100)
 */
private $job;

I must be missing something here?

Answer 1

Found out where the error was:

The Person->id property has to be mapped too. Also, order of columns in SELECT clause has to match the order of addFieldResult() statements.

Therefore, $sql should look like this:

SELECT a.id, a.job, p.id AS personId, p.first_name, p.last_name 
FROM application a 
    INNER JOIN person p ON a.person_id=p.id

And mapping for related property like this:

    $rsm->addJoinedEntityResult('\Company\Department\Domain\Model\Person' , 'p', 'a', 'person');
    $rsm->addFieldResult('p','personId','id');
    $rsm->addFieldResult('p','first_name','firstName');
    $rsm->addFieldResult('p','last_name','lastName');

So, the mapped field result column name corresponds to sql result column name, and third parameter, id in this case, should be the property actual name.