Symfony2 / Doctrine：检索记录时引发其他实体的问题

I have the following situation: - I record "HR management" events (changing roles, assignation to projects, deactivations) - I have a "Roles" table where I have a list of roles. Each role has the following fields

/**
* @ORM\Entity(repositoryClass="AppBundle\Entity\RoleRepository")
* @ORM\Table(name="roles")
* @UniqueEntity(fields={"name","project"}, ignoreNull=false, message="Duplicated role for this project")
*/
class Role
{
/**
 * @ORM\Column(type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

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

/**
 * @ORM\Column(type="boolean")
 */
protected $Personal_profile__view;
/**
 * @ORM\Column(type="boolean")
 */
protected $Personal_profile__change;

...many other boolean permissions

/**
 * @ORM\ManyToOne(targetEntity="Project")
 */
protected $project;

Roles are usually linked to no Project (hence the last field can be null) but can also be project dependent (for custom roles).

I run the following query to get all the projects related to a user:

    $projects = $this->getEntityManager()
        ->createQuery(
        'SELECT p.id projectID, p.name, p.description, h.is_active resourceIsActive, r role, h.timestamp
         FROM AppBundle:Project p, AppBundle:HRMgmtEvent h, AppBundle:Role r
         WHERE h.user = :userid
         AND h.project=p.id
         AND h.role=r.id
         ORDER BY h.timestamp ASC'
        )->setParameter('userid', $user->getId())
         ->getResult();

I want to be able to retrieve the whole "Role" r, so that then in my controller I can do a "get" of any of the booleans permission associated with it.

All works like a charm if all "r" are different (basically if the role_id of HRMgmnt event are different). If they are the same something very strange happens: the query that I read in the profiler retrieves correctly the information (retrieving 2 records, for instance), but when that info is mapped back in PHP, I only have one record.

If on the other hand, instead of retrieving the whole entity Role r, I retrieve role.name role.whatever then I have no issues.

Any idea how to fix it?

Thanks!

My HRMgmt entity is the following:

/**
 * @ORM\Entity(repositoryClass="AppBundle\Entity\HRMgmtEventRepository")
 * @ORM\Table(name="HRMgmt")
 */
class HRMgmtEvent
{
/**
 * @ORM\Column(type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

/**
 * @ORM\Column(type="datetime")
 */
protected $timestamp;

/**
 * @ORM\ManyToOne(targetEntity="User")
 */
protected $user;

/**
 * @ORM\ManyToOne(targetEntity="Project")
 */
protected $project;

/**
 * @ORM\Column(type="datetime", nullable=true)
 */
protected $started_at;

/**
 * @ORM\Column(type="datetime", nullable=true)
 */
protected $finished_at;

/**
 * @ORM\Column(type="boolean")
 */
protected $is_active;

/**
 * @ORM\ManyToOne(targetEntity="User")
 */
protected $changed_by;

/**
 * @ORM\ManyToOne(targetEntity="Role")
 */
protected $role;

EDIT:

I substituted my query using JOIN as suggested:

'SELECT p.id projectID, p.name, p.description, h.is_active resourceIsActive, r role, h.timestamp FROM AppBundle:HRMgmtEvent h
 INNER JOIN h.project p
 INNER JOIN h.role r
 WHERE h.user = :userid
 ORDER BY h.timestamp ASC'

If i leave it this way I get the error:

[Semantical Error] line 0, col -1 near 'SELECT p.id projectID,': Error: Cannot select entity through identification variables without choosing at least one root entity alias.

If I instead retrieves "r.name rolename" (leaving the JOIN) I have no problems.

EDIT2:

Doing some additional research I thought about using "partial" as follows:

'SELECT  partial p.{id, name, description}, partial h.{project, role, is_active, timestamp}, r FROM AppBundle:HRMgmtEvent h
 INNER JOIN h.project p
 INNER JOIN h.role r
 WHERE h.user = :userid
 ORDER BY h.timestamp ASC'

But I get the following:

[Semantical Error] line 0, col 89 near '}, r FROM AppBundle:HRMgmtEvent': Error: The partial field selection of class AppBundle\Entity\HRMgmtEvent must contain the identifier.

Note that I included both foreign keys of "h" pointing at "p" and "r".

I finally figured it out, I had to modify my query as follows:

SELECT  p.id projectID, p.name, p.description, partial h.{id, project, role, is_active, timestamp} hrevent, r FROM AppBundle:HRMgmtEvent h
 INNER JOIN h.project p
 INNER JOIN h.role r
 WHERE h.user = :userid
 ORDER BY h.timestamp ASC

The reason why I'm retrieving the fields of "p" like that and not with partial is that I did not want to change the logic I already wrote after the query that expected data in that format, but I tested and it worked also with partial on "p".

Notice that the only difference between this solution and the one presented in EDIT2 is that I'm also retrieving the id of "h". I did not find any refence about it in the documentation here http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#partial-object-syntax