Using partial and non-partial select from a join in doctrine

Question

My Querybuilder statement looks like this:

$qb->from('models\Order', o');
$qb->innerJoin('o.fStatus', 'fs');
$qb->select('COUNT(o.id), PARTIAL fs.{name, id}');

If I run this I get the error

SELECT COUNT(o.id),': Error: Cannot select entity through identification variables without choosing at least one root entity alias.

However, if I change my select statement to either of these:

$qb->select('PARTIAL o.{id}, PARTIAL fs.{name, id}');
$qb->select('COUNT(o.id), fs.name, fs.id');

The query will run.

Why can I not select from the root entity and also a partial object that has been joined to it?

Answer 1

Doctrine gives a bit of explanation in their documentation:

A common mistake for beginners is to mistake DQL for being just some form of SQL and therefore trying to use table names and column names or join arbitrary tables together in a query. You need to think about DQL as a query language for your object model, not for your relational schema.

When you select using DQL or the QueryBuilder, it is traditionally expecting you to select the root entity (the one in your FROM clause), or some combination of columns/aggregates (COUNT, SUM, etc.). When you select a partial object from a joined table but don't select the root entity, Doctrine doesn't know how to hydrate - what if there is a one-to-many/many-to-one, how does it return the data? It won't make those assumptions.

Doctrine by default does not allow partial objects. It seems like you would be better off just returning columns for your query since that's really what you're looking for in that case.

Others have worked around the issue using the WITH clause - see Doctrine query distinct related entity.