6

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.

Jason Roman
  • 8,146
  • 10
  • 35
  • 40
Performation
  • 63
  • 1
  • 4
  • Something is wrong with either your query builder (most likely) or your entity. Paste more code on these two. – Don Omondi Dec 29 '15 at 16:10
  • That are all the relevant sections. Query Builder code is in the repository. I return return $q->getQuery()->getResult(); That's all. – Performation Dec 29 '15 at 18:03
  • Normal query builder in repo goes like $this->getEntityManager()->getRepository('MyBundle:Entity')->createQueryBuilder('c') – Don Omondi Dec 29 '15 at 18:56
  • The `getEntityManager()` and `getRepository()` calls can be omitted when already in the repository you are selecting from. – Jason Roman Dec 29 '15 at 22:38

2 Answers2

4

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();
}
Jason Roman
  • 8,146
  • 10
  • 35
  • 40
  • 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)? – Performation Dec 29 '15 at 23:17
  • 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. – Jason Roman Dec 29 '15 at 23:23
  • @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... – Greg Bell Jul 17 '17 at 07:14
  • I modified my answer to be more clear that I meant selecting an entity. – Jason Roman Jul 17 '17 at 11:55
-1

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;
Don Omondi
  • 946
  • 9
  • 15
  • 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. – Performation Dec 29 '15 at 20:26
  • 1
    the `getEntityManager()` and `getRepository()` calls aren't necessary if you are already in the custom repository. – Jason Roman Dec 29 '15 at 22:37