Using parentheses (>) in Symfony 2.5 and the Doctrine Query Builder

Question

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

• QueryException: [Syntax error] row 0, column 19: Error: expected doctrine \ ORM \ Query \ Lexer :: T_CLOSE_PARENTHESIS received by '>'

  $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 logical conditions inside aggregate functions. You can use beberlei/DoctrineExtensions , or if you do not want to install the entire library, just add one IF condition: https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/IfElse.php .

To register a 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 cannot specify a condition in the Doctrine2 DQL, BTW amount selection operator; you can filter only those who have a sum greater than your limit. Try the following:

 $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 for this help

Answer 3

Thanks guys. Finally, it seems that I decided myself. Also I'm not sure if this is correct. While he shows the correct answer.

The original MySQL query that works just fine:

 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 converting it to Dql. Unfortunately, because you guys weren't able to use parentheses in the Doctrine aggregate function. So I decided. Now he counts all the tables A, B, C, D, Fs from the score table. And the class information (letter and number) was extracted from its parent table. Grades A, B, C, D, F are not exactly the field I wanted. This is the same as the image below.

It could be a 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();