Doctrine 2 does not recognize SELECT on the FROM clause

Question

I have a SQL query I would like to use in Doctrine2.

My query, goes to the dabatase, does a GROUP BY user state and COUNT the number of users per state.

Then I'm trying to join a table to COUNT all users and make a percent for every state.

return $this->getEntityManager()
            ->createQuery("
                SELECT COUNT(u.id) as total, 
                    (100*(COUNT( u.id ) /  total_users.total)) as percent
                FROM UserBundle:User u,
                    (SELECT COUNT(*) as total
                     FROM UserBundle:User) as total_users
                LEFT JOIN u.idUserEstado ue
                GROUP BY u.idUserEstado")
            ->getResult();

The problem is, when I run the Doctrine2 query I get an exception:

[Semantical Error] line 0, col 397 near 
'(SELECT COUNT(': Error: Class '(' is not defined. 

Doctrine does not recognize that SELECT on the FROM clause.

Answer 1

Select on the from clause are not handled by doctrine 2

There is a closed (and not accepted) feature request on doctrine's jira : http://www.doctrine-project.org/jira/browse/DDC-2793

DQL is about querying objects. Supporting subselects in the FROM clause means that the DQL parser is not able to build the result set mapping anymore (as the fields returned by the subquery may not match the object anymore). This is why it cannot be supported (supporting it only for the case you run the query without the hydration is a no-go IMO as it would mean that the query parsing needs to be dependant of the execution mode).

Answer 2

(SELECT COUNT( * ) as total FROM UserBundle:User) as total_users

COUNT(*) may not exists in DQL. Try COUNT(u.id), the results will be the same. You also have the possibility to try this in an EntityRepository :

$qb->select(array(
    'count(u.id) as total',
    '(100*(count(u.id)/total_users.total)) as percent',
    'select count(u.id) as total_users) as total_users'))
   ->from('UserBundle:User')
   ->leftJoin('u.idUserEstado','ue')
   ->groupBy('u.idUserEstado');

return $qb->getQuery()->getResult();

Source: Google UserGroup issue