CODEWITHSUNDEEP
mysqlfunctionsymfonydoctrine-ormsum
Johny
Johny

Reputation: 119

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

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:

Upvotes: 5

Views: 1126

Answers (3)

Johny
Johny

Reputation: 119

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. enter image description here

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();

Upvotes: 0

Matteo
Matteo

Reputation: 39390

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

Upvotes: 1

b.b3rn4rd
b.b3rn4rd

Reputation: 8830

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

Upvotes: 1

Related Questions