Symfony2 recursive query builder

I assume you have defined an entity class for your table using Doctrine annotations. eg.

/** * @ORM\Entity * @ORM\Table(name="folder") */class Folder{/** * @ORM\Column(type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */private $id;/** * @ORM\Column(name="nameDisplay", type="string", length=100) */private $nameDisplay;/** * parentID * @ORM\Column(name="parentID", type="integer", nullable=true) */private $parentId;}

You can use "createNativeQuery()" of your entity manager to achieve what you want:

use Doctrine\ORM\Query\ResultSetMapping;...$rsm = new ResultSetMapping();$rsm->addEntityResult(\AppBundle\Entity\Folder::class, 'fd');$rsm->addFieldResult('fd','id','id');$rsm->addFieldResult('fd','nameDisplay','nameDisplay');$query = $this->_em->createNativeQuery(        'SELECT @id :=(SELECT parentID FROM ' . $this->_entityName .' WHERE id = @id) as id,(SELECT nameDisplay FROM ' . $this->_entityName .' WHERE id = @id) as nameDisplay    FROM (SELECT  @id := ?) vars    JOIN ' . $this->_entityName .' fd    WHERE @id IS NOT NULL', $rsm);$query->setParameter(1, 8);$folderStructure = $query->getResult();

Replace \AppBundle\Entity\Folder with your entity class and you should receive an array with entities ordered to the structure you want.I could not map also the parentID to the result, but I believe that should be enough.

Original query that was used as reference can be found here