Symfony2 recursive query builder

Question

I am having a data-table which contains media,docs,pdfs and folders list. Same as Google Drive, I want to show breadcrumb at the top of the data-table. The data-table reflects the changes when clicks on any folders through jQuery. All things are ready except the breadcrumb. I have the id of current opened folder but I don't know how to get the hierarchy from root to current folder.

Currently it gives me the parent id and name not the whole hierarchy.

Note :- There is no relationship between the columns as it was in same table.
Entity Fields :- id, nameDisplay, parentId

Entity Definition :-

/**
 * driveall
 * @ORM\Table(name="Driveall")
 * @ORM\Entity(repositoryClass="DriveBundle\Repository\DriveallRepository")
 */
class Driveall
{
    /**
     * @var int
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

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

    /**
     * @var int
     * @ORM\Column(name="ParentID", type="integer")
     */
    private $parentID;

    /**
     * Get id
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set nameDisplay
     * @param string $nameDisplay
     * @return driveall
     */
    public function setNameDisplay($nameDisplay)
    {
        $this->nameDisplay = $nameDisplay;
        return $this;
    }

    /**
     * Get nameDisplay
     * @return string
     */
    public function getNameDisplay()
    {
        return $this->nameDisplay;
    }

    /**
     * Set parentID
     * @param integer $parentID
     * @return Driveall
     */
    public function setParentID($parentID)
    {
        $this->parentID = $parentID;
        return $this;
    }

    /**
     * Get parentID
     * @return integer
     */
    public function getParentID()
    {
        return $this->parentID;
    }
}  

Query builder :-

$qb = $this->_em->createQueryBuilder();
$qb->select("d.parentID,pid.nameDisplay")
   ->from($this->_entityName, 'd')
   ->leftJoin($this->_entityName, 'pid', 'WITH', 'pid.id = d.parentID')
   ->where("d.status=0")
   ->andWhere("d.id=" . $id)
   ->orderBy('d.nameDisplay', 'ASC');
$data = $qb->getQuery()
           ->getResult();
return $data;

Answer 1

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