Symfony2/Doctrine: problems retrieving records with references to other entities

Question

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".

Consider using DQL: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html — Cerad, Jan 14 '15 at 15:24
Look at the examples in the documentation to see how to join entities. — Cerad, Jan 14 '15 at 17:50
@Cerad please see updated question (bottom part after the EDIT). — Sergio Negri, Jan 15 '15 at 10:23
My query is possibly not doable (http://www.doctrine-project.org/jira/browse/DDC-1001). I wonder why all these limitations of Doctrine (not the first one I encounter...) — Sergio Negri, Jan 15 '15 at 11:21
Doctrine 2 ORM is based on objects, not sql. It does require shifting your thinking a bit. Start by selecting the objects instead of spelling out individual columns (see the examples in the documentation). More than likely your inner joins should be left joins. — Cerad, Jan 15 '15 at 12:50
Performance wise if I need only some fields it's better to select some fields, on the other hand when I'm precisely trying to select an entire object "r role" that's when I have problems. I do select entire objects in other queries without issues, the problem is only here when I am retrieving both objects AND some fields of another entity...I might try the "partial" thing though...but I suspect I won't solve the issue... — Sergio Negri, Jan 15 '15 at 13:17
Compare the performance of a working solution to a non-working one. — Cerad, Jan 15 '15 at 17:04
I already have a working AND performing solution. It's a nonsense having to retrieve all objects if I need one object and some just fields of the other. I tried with "partial", also following this example http://stackoverflow.com/questions/13844970/no-mapped-field-when-using-partial-query-and-composite-keys-in-doctrine2 but still having issues (see EDIT2) — Sergio Negri, Jan 15 '15 at 21:15
It's great that you found the solution. You should probably post it and close the question. Just be sure to bookmark it and review in a year or so. Assuming you continue with Doctrine 2, I suspect you might have a different opinion on the usefulness of only retrieving certain properties. — Cerad, Jan 15 '15 at 22:05
The solution (which was a workaround actually) was already present in my original post. Now I found the "real" solution and posted it as answer — Sergio Negri, Jan 16 '15 at 08:20

score 0 · Answer 1 · answered Jan 16 '15 at 08:12

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

Symfony2/Doctrine: problems retrieving records with references to other entities

1 Answers1