Doctrine2 One-To-Many, Self-referencing relationship

Question

In my 'Topic' entity, I have a One-To-Many, Self-referencing relationship $parent:$children.

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

    /** @Column(length=40, unique=true) */
    private $name;

    /**
     * @ORM\ManyToOne(targetEntity="Topic", inversedBy="children")
     */
    private $parent;

    /**
     * @ORM\OneToMany(targetEntity="Topic", mappedBy="parent")
     */
    private $children;
}

I can join the table to get the parent-children hierarchy like this:

return $this->getEntityManager()->createQuery('
    SELECT t, c FROM My\xxxBundle\Entity\Topic t 
    LEFT JOIN t.children c
    WHERE t.parent IS NULL
')
->getArrayResult();

Here's the correct output:

array
  0 => 
    array
      'id' => int 1
      'name' => string 'Parent 1'
      'slug' => string 'p-1'
      'description' => null
      'children' => 
        array
          0 => 
            array
                'id' => int 2
                'name' => string 'Child 1-1'
                'slug' => string 'c-1-1'
                'description' => null
          1 => 
            array
                'id' => int 3
                'name' => string 'Child 1-2'
                'slug' => string 'c-1-2'
                'description' => null
  1 => 
    array
      'id' => int 4
      'name' => string 'Parent 2'
      'slug' => string 'p-2'
      'description' => null
      'children' => 
        array
          empty
...

but if I try to fetch specific columns in the SELECT statement:

SELECT t.name, c.name FROM My\xxxBundle\Entity\Topic t

I get a flat array of child entities i.e only c.name. If a parent has no children, I just get a null value for its name:

  1 => 
    array (size=1)
      'name' => string 'Child 1-1' (length=14)
  2 => 
    array (size=1)
      'name' => string 'Child 1-2' (length=14)
  3 => 
    array (size=1)
      'name' => null
  4 => 
    array (size=1)
      'name' => string 'Child 3-1' (length=5)

On Mark's suggestion, I've renamed the name field of child entity:

SELECT t.name, c.name AS child_name FROM My\xxxBundle\Entity\Topic t

but I still get the wrong format:

array
  0 => 
    array
      'name' => string 'Parent 1'
      'child_name' => string 'Child 1-1'
  1 => 
    array
      'name' => string 'Parent 1'
      'child_name' => string 'Child 1-2'
  2 => 
    array
      'name' => string 'Parent 2'
      'child_name' => string 'Child 2-1'

Answer 1

The problem is that you select two fields with the same name (the name field from the topic and the name field from the children). These names conflict with each other when creating the array keys. The solution to this problem is by renaming one of the fields:

SELECT t.name, c.name AS child_name FROM My\xxxBundle\Entity\Topic t

The reason you get a flat array, is because you are not selecting the entities, but individual values from these entities.

See for more information the DQL documentation: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#select-queries

Edit The current result you get from the query is logical. It matches what you would get when you would run the equivalent SQL query. What you are currently doing is fetching two values from two database tables. What you really want are two partial entities formatted in an array.

What you can try is using Partials. This is a way in doctrine to select just a few fields of an entity. Of you combine this with ->getArrayResult() you probably get the right output

return $this->getEntityManager()->createQuery('
    SELECT partial t.{name}, partial c.{name}
    FROM My\xxxBundle\Entity\Topic t 
    LEFT JOIN t.children c
    WHERE t.parent IS NULL
')
->getArrayResult();