Doctrine2: [Semantical Error] Cannot select entity through identification variables without choosing at least one root entity alias

Question

This is my query with query builder, and it works perfectly, bringing all the results of user table and the modules table, which has a many to many association:

public function getUser($id){
    $qb = $this->getEm()->createQueryBuilder()
    ->select('u', 'm')
    ->from('Adm\Entity\User', 'u')
    ->join('u.modules', 'm')
    ->where('u.id = ?1')
    ->setParameters(array(1 => $id));
    $result = $qb->getQuery()->getResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
    return $result;
}

But when i try to select specific fields from user like this:

public function getUser($id){
    $qb = $this->getEm()->createQueryBuilder()
    ->select('u.id, u.name, u.email', 'm')
    ->from('Adm\Entity\User', 'u')
    ->join('u.modules', 'm')
    ->where('u.id = ?1')
    ->setParameters(array(1 => $id));
    $result = $qb->getQuery()->getResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
    return $result;
}

Doctrine throws an error:

[Semantical Error] line 0, col -1 near 'SELECT u.id,': Error: Cannot select entity through identification variables without choosing at least one root entity alias.

I would like to know how to do that, to select specific fields from the entity and not all the fields.

Concessionaire

Answer 1

The problem with your second query is that you are trying to return the associated modules objects but not the User object. Doctrine doesn't like this, and doesn't operate this way. I know you tried to hydrate an array, but if you hadn't, this is what your first query would be trying to return:

User object {
    ...,
    $modules -> array of Module objects
}

So you'd return a single User object, and then your $modules member of that User class is going to be an array of all associated module objects. Even though you are selecting u, m, Doctrine still wants to return that one object because m is just an association on u. Just because you want to hydrate an array doesn't change how Doctrine wants to select your data to begin with.

Your second example - Doctrine doesn't know how to return that. By individually selecting User fields, you are no longer returning a User object but an array of User values. So, where could it even return associated modules there? It wouldn't make sense to return this format:

[
    user id,
    user name,
    user email,
    [ array of Module objects ]
]

This is even trickier as a Many-to-Many association. The real question is, what are you trying to accomplish with your second query that you find unacceptable with your first query? If it's performance, you're probably not gaining much with the second query. If it's simply returning specific columns, then you should be specifying those m. columns as well.

Could

Hi @Jason Roman, thanks for your excellent answer. My second query is due the fact i would not like to bring all fields from user table/entity, for example: password, since i'm consuming this query in javascript, and it would be exposed in browser console. – Concessionaire

Sounds good - then just make sure to select the individual columns you need all around and not just for the User entity. You can also look at the JMSSerializerBundle github.com/schmittjoh/JMSSerializerBundle for better granularity of what you return...for example you could flag your password field with @Exclude. See: github.com/schmittjoh/JMSSerializerBundle – Could

Thanks for the tips @Jason Roman, if i try to do something like this: ->select('u.id, u.name', 'm.id, m.name') the query brings me just first result from 'm', unlike ->select('u.', ' m') which brings me a correct array of 'm'. – Concessionaire

Interesting - in your case I would either run a native SQL call, and return that result, or, use your first attempt and exclude fields you don't need with a serializer like I described – Could

Thanks @Jason Roman, i'll give a try at serializer and native SQL. – Concessionaire

@JasonRoman - you said "Doctrine doesn't like this, and doesn't operate this way" Can you elaborate? And (meta) how do you know this? – Hashish

The rest of my answer elaborated what I meant, explaining how Doctrine works. I know it from the Doctrine documentation and examples on Doctrine's website. – Could

What I've deduced is that the SELECT determines what gets hydrated, and the FROM determines what entities are returned in the result array. (For some reason), you have to hydrate what you're returning, at a minimum. Not sure why lazy-load proxy objects wouldn't be acceptable. – Hashish