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

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

Answer 2

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 3

Thank you guys. Finally it seems that I solved on my own. Also I am not sure it is correct. For now it shows correct answer.

Original MySQL query which works perfect:

SELECT avg(final) as Final, SUM(Final>89.5) as a, SUM(Final<89.5 AND Final>79.5) as b, SUM(Final<79.5 AND Final>69.5) as c, SUM(Final<69.5 AND Final>59.5) as d , SUM(Final<59.5) as f, Ca.letter, Ca.number, subject_id FROM grades as G join courses as C on G.course_id = C.id join schoolclasses as Ca on Ca.id=C.schoolclass_id where C.subject_id = 1 and G.quarter_id in (1, 2) group by G.course_id

I tried it convert to Dql. Unfortunately due to you guys parenthesis was unable to use in aggregated function of Doctrine. This is the way how I solved. Now it counts all A, B, C, D, Fs from grades table. And School class (letter and number) information was retrieved from its parent table. The grades A, B, C, D, F are not exactly field which I wanted. It is same as image below.

It could be kind of trick

$rsm = new ResultSetMapping();
            $rsm->addEntityResult('Ficus\EschoolBundle\Entity\Grade', 'g');
            $rsm->addFieldResult('g', 'Final', 'final');
            $rsm->addFieldResult('g', 'A', 'a');
            $rsm->addFieldResult('g', 'B', 'b');
            $rsm->addFieldResult('g', 'C', 'c');
            $rsm->addFieldResult('g', 'D', 'd');
            $rsm->addFieldResult('g', 'F', 'abcd');
            $rsm->addFieldResult('g', 'Class', 'a1');
            //$query = $this->getEntityManager()->createNativeQuery('SELECT avg(final) as Final, SUM(Final>89.5) as a, SUM(Final<89.5 AND Final>79.5) as b, SUM(Final<79.5 AND Final>69.5) as c, SUM(Final<69.5 AND Final>59.5) as d , SUM(Final<59.5) as f, Ca.letter, Ca.number, subject_id FROM grades as G join courses as C on G.course_id = C.id join schoolclasses as Ca on Ca.id=C.schoolclass_id where C.subject_id = 1 and G.quarter_id=1 group by G.course_id', $rsm);
            $query = $this->getEntityManager()->createNativeQuery(''
                    . 'SELECT avg(g.final) as Final, '
                    . 'SUM(Final>89.5) as A, '
                    . 'SUM(Final<89.5 AND Final>79.5) as B, '
                    . 'SUM(Final<79.5 AND Final>69.5) as C, '
                    . 'SUM(Final<69.5 AND Final>59.5) as D, '
                    . 'SUM(Final<59.5) as F, '
                    . 'CONCAT(ca.number, ca.letter) as Class '
                    . 'FROM grades as g '
                    . 'JOIN courses as c on g.course_id = c.id '
                    . 'JOIN schoolclasses as ca on ca.id = c.schoolclass_id '
                    . 'WHERE c.subject_id = ? AND g.quarter_id in (?) group by g.course_id '
                    . 'ORDER BY ca.number, ca.letter ', $rsm);
            $query->setParameter(1, $rid);
            $query->setParameter(2, $filterQuarter);

            $grades = $query->getArrayResult();