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.

Urceolate

Something is wrong with either your query builder (most likely) or your entity. Paste more code on these two. – Doradorado

That are all the relevant sections. Query Builder code is in the repository. I return return $q->getQuery()->getResult(); That's all. – Urceolate

Normal query builder in repo goes like $this->getEntityManager()->getRepository('MyBundle:Entity')->createQueryBuilder('c') – Doradorado

The getEntityManager() and getRepository() calls can be omitted when already in the repository you are selecting from. – Cusk

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();
}

Cusk

Thanks for explaining. Is there any workaround? My controller serializes the whole object for a json and for security reasons I only want to select some of the fields. I could of course do this selection in the Serializer but is there no way to only gather the data I want from the database (i.e. c.name and the child object)? – Urceolate

The Serializer component should allow you to set groups on your entities and then serialize just those specific groups. The issue with your association to children is that there could be more than one, otherwise I would say to simply select individually joined fields from the other object. You could do that, but it would depend on what your JSON object expects. I would give the Serializer component a closer look, groups may be the answer. – Cusk

@JasonRoman you said "you can't select only from the table you are joining on..." But he wasn't, was he? He was selecting from cc (the join object) and c.name... – Crankle

I modified my answer to be more clear that I meant selecting an entity. – Cusk

Answer 2

Your query is missing some parts. Normal query builder in repo goes like

    $q = $this->getEntityManager()
        ->getRepository('MyBundle:Entity')
        ->createQueryBuilder(‌​'c')
        ->select(‌​'c')
        ->leftjoin(‌​'c.children', 'cc')
        ->addselect(‌​'cc')
        ->where(‌​'c.parent is NULL')
....
    return $q;

Doradorado

Thanks, but your query throws the same error when i change select('c') to select('c.name as title'). First parts of our query are equivalent if called from the repo of the entity I am using. – Urceolate

the getEntityManager() and getRepository() calls aren't necessary if you are already in the custom repository. – Cusk