Doctrine2 Self Reference Query — Doesn't work

Question

$ I am trying to create following scenario with doctrine 2 query builder

SELECT 
    p . *
FROM
    _tree p
    LEFT JOIN
    _tree c ON p.id = c.parent_id
       AND (c.lft >= p.lft AND c.rgt <= p.rgt)
WHERE
    p.id = 3

I have set following relationship self generated by Doctrine2

    class Tree {

    /**
     * @var \Tree
     *
     * @ORM\ManyToOne(targetEntity="Tree")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="parent_id", referencedColumnName="id")
     * })
     */
    private $parent;

      // other code
    }

here is my repo class

_em->createQueryBuilder();
        $qb->select('p')
           ->from('Entity\Tree', 'p')
           ->leftJoin('p.Entity\Tree','c', 'ON','p.id = c.parent_id');

        return $qb->getQuery()->getResult();
    }
}

but I couldn't get it done. It throws following errors

[Tue Oct 01 22:30:11 2013] [error] [client 127.0.0.1] PHP Fatal error: Uncaught exception 'Doctrine\ORM\Query\QueryException' with message 'SELECT p FROM Entity\Tree p LEFT JOIN p.Entity\Tree c ON p.id = c.parent_id' in /var/www/pcb_frame_work/System/Libraries/Vendors/Doctrine/ORM/Query/QueryException.php:39\nStack trace:\n#0 /var/www/pcb_frame_work/System/Libraries/Vendors/Doctrine/ORM/Query/Parser.php(429): Doctrine\ORM\Query\QueryException::dqlError('SELECT p FROM E...')\n#1 /var/www/pcb_frame_work/System/Libraries/Vendors/Doctrine/ORM/Query/Parser.php(925): Doctrine\ORM\Query\Parser->semanticalError('Class Entity\Ed...')\n#2 /var/www/pcb_frame_work/System/Libraries/Vendors/Doctrine/ORM/Query/Parser.php(1561): Doctrine\ORM\Query\Parser->JoinAssociationPathExpression()\n#3 /var/www/pcb_frame_work/System/Libraries/Vendors/Doctrine/ORM/Query/Parser.php(1506): Doctrine\ORM\Query\Parser->JoinAssociationDeclaration()\n#4 /var/www/pcb_frame_work/System/Libraries/Vendors/Doctrine/ORM/Query/Parser.php(1435): Doctrine\ORM\Query\Parser->Join()\n#5 /var/www/pcb_frame_work/System/Librari in /var/www/pcb_frame_work/System/Libraries/Vendors/Doctrine/ORM/Query/QueryException.php on line 49, referer:

Can you add the complete definition of your Tree entity? Maybe that gives a clear view of the left and right columns you use in you example query — Rene Terstegen

Rene Terstegen Rene Terstegen · Accepted Answer · 2013-10-02T12:55:42

I don't know if I understand you completely, but I think you have to change your ManyToOne relation to:

/**
 * @var \Tree
 *
 * @ORM\ManyToOne(targetEntity="Tree", inversedBy="children")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="parent_id", referencedColumnName="id")
 * })
 */
private $parent;

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

That way you can access the children of a class with $Tree->children and it's parent with $Tree->parent.

More information about self referencing associations can be found here: http://docs.doctrine-project.org/en/latest/reference/association-mapping.html#one-to-many-self-referencing

Doctrine2 Self Reference Query — Doesn't work

1 Answers