Symfony3.4 / Doctrine : subquery in FROM Clause : Error: Class 'SELECT' is not defined

Question

I'm working on a Symfony 3.4 project and I'm trying to translate an sql query to DQL query but I get an Issue.

Mysql Query:

select sum(montant_paye) 
from 
    (select montant_paye 
     from vente 
     where client_id = 1
     and montant_paye > 0
     order by date ASC 
     limit 2) 
     as T;

DQL Query (Error):

return $this->getEntityManager()
  ->createQuery('
      SELECT SUM(montantPaye) as Total
      FROM
        SELECT v.montantPaye
        FROM AppBundle:Vente v
        where v.montantPaye > 0
        AND v.client = '.$clientId.'
        ORDER BY v.date ASC
        limit 2
  ')
  ->getResult();

Error :

[Semantical Error] line 0, col 71 near 'SELECT v.montantPaye
': Error: Class 'SELECT' is not defined.

Is any one have a solution for a correct DQL query ?

Pretty sure you still need parens around the inner select. Might still have other problems. And of course inlining $clientId is a bad thing. — Cerad, Aug 03 '18 at 18:03
In this case doctrine understands 'SELECT' as an entity class. I'm almost sure that enclosing subquery in parenthesis gonna solve this issue. — Omar Alves, Aug 03 '18 at 18:17

score 1 · Answer 1 · answered Aug 03 '18 at 20:59

Quoting from Christophe stoef Coevoet (Symfony Core Developer):

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).

In your case, the best solution is probably to run a SQL query instead (as you are getting a scalar, you don't need the ORM hydration anyway)

Details here.

score 1 · Answer 2 · answered Aug 04 '18 at 11:09

add this function to your VenteRepository:

public function sumMontantPaye($clientId)
{
    return $this->createQueryBuilder("v")
        ->select("sum(v.montantPaye) as sum")
        ->where("v.client = :id")
        ->andWhere("v.montantPaye > 0")
        ->setParameter("id", $clientId)
        ->setMaxResults(2)
        ->getQuery()->getSingleResult();
}

you can access the sum using $result["sum"] assuming $result is the variable assigned to this function in the controller

Symfony3.4 / Doctrine : subquery in FROM Clause : Error: Class 'SELECT' is not defined

2 Answers2