Using parentheses ( > ) in Symfony 2.5 and Doctrine query builder

Question

I am working on the small project using Symfony 2.5 and Doctrine. My query runs in MySQL Workbench perfectly. Unfortunately in doctrine I get error below when I use parentheses in my query builder:

• QueryException: [Syntax Error] line 0, col 19: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '>'

  $grades = $qb
                  ->select(array(
                      'SUM(g.final > 89.5) as a',                            
                      'CONCAT (gcs.number, gcs.letter) as class'
                  ))
                  ->from('FicusEschoolBundle:Grade', 'g')
                  ->leftJoin('g.course', 'gc')
                  ->leftJoin('gc.schoolclass', 'gcs')
                  ->where($qb->expr()->eq('gc.subject', $rid))
                  ->andWhere($qb->expr()->in('g.quarter', $filterQuarter))
                  ->groupBy('gc')
                  ->orderBy('gcs.number')
                  ->getQuery()
                  ->getArrayResult();
  

Answer 1

You can't specify a condition in the Doctrine2 DQL sum select operator, BTW you can filter only who have the sum greater than your limit. Try this:

$grades = $qb
                ->select(array(
                    'SUM(g.final) as a',                            
                    'CONCAT (gcs.number, gcs.letter) as class'
                ))
                ->from('FicusEschoolBundle:Grade', 'g')
                ->leftJoin('g.course', 'gc')
                ->leftJoin('gc.schoolclass', 'gcs')
                ->where($qb->expr()->eq('gc.subject', $rid))
                ->andWhere($qb->expr()->in('g.quarter', $filterQuarter))
                ->having(
                    $qb->expr()->gt(
                                 $qb->expr()->sum('g.final'), 89.5
                                   )
                 )
                ->groupBy('gc')
                ->orderBy('gcs.number')
                ->getQuery()
                ->getArrayResult();

Hope this help

Answer 2

By default Doctrine does not allow to have logical conditions inside aggregate functions. You can use beberlei/DoctrineExtensions or if you dont want to install the whole library just add single IF condition: https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/IfElse.php.

To register custom DQL function:

# app/config/config.yml
doctrine:
    orm:
        # ...
        dql:
            string_functions:
                test_string: AppBundle\DQL\StringFunction
                second_string: AppBundle\DQL\SecondStringFunction
            numeric_functions:
                test_numeric: AppBundle\DQL\NumericFunction
            datetime_functions:
                test_datetime: AppBundle\DQL\DatetimeFunction

Source: http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html