Doctrine2: Cannot select entity through identification variables without choosing at least one root entity alias

Question

I am stuck with an originally very simple doctrine 2 query. I have an entity called Category which has a OneToMany relation with itself (for parent and child categories).

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

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

The following query

$q = $this->createQueryBuilder('c')
            ->leftJoin('c.children', 'cc')
            ->select('c.name as title, cc')
            ->where('c.parent IS NULL');

fails with error

Cannot select entity through identification variables without choosing at least one root entity alias.

I don't really get the issue. If I omit the ->select part, the query does work and gives the expected result. I have already searched the forum but couldn't find a solution, that worked. Does anyone have a suggestion? Thanks a lot.

Answer 1

Your issue is that you are trying to select one field from the Category entity while simultaneously selecting the entire object of the joined Category entity. Unlike plain SQL, with the QueryBuilder component you can't select an entity only from the table you are joining on.

If you are looking to return your main Category object with the joined children, you can either do ->select(array('c', 'cc')), or simply omit the ->select() call altogether. The former will automatically select the children you need in a single query. The latter will require another SQL query if you want to access children on the main Category entity.

If there is a reason you want name to select as title in your object, you can always add another function to your entity that is an alias for retrieving the name instead of having to write it in your query:

function getTitle()
{
    return $this->getName();
}