在Symfony 2.5和Doctrine查询构建器中使用括号(>)

Question

我正在使用Symfony 2.5和Doctrine进行小型项目的开发。在MySQL Workbench中，我的查询可以完美运行。不幸的是，在我的查询构建器中使用括号时，我会遇到以下错误：

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

- Johny

Answer 1

默认情况下，Doctrine不允许在聚合函数内部使用逻辑条件。您可以使用beberlei/DoctrineExtensions，或者如果您不想安装整个库，只需添加单个IF条件：https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/IfElse.php。

要注册自定义DQL函数：

# 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

来源：http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html

- b.b3rn4rd

Answer 2

您无法在Doctrine2 DQL sum选择运算符中指定条件，但您可以仅过滤总和大于您的限制的内容。尝试这样做：

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

希望这有所帮助。

- Matteo

---

你的查询会产生错误的结果，因为 SUM(g.final > 89.5) 不等同于 SUM(g.final) > 89.5。 - M Khalid Junaid

是的，我知道，我把这个查询描述为一种解决方法，感谢您的澄清。 - Matteo

Answer 3

谢谢大家。最终似乎我自己解决了问题。但我不确定是否正确。目前它显示了正确的答案。

原始的MySQL查询非常完美：

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

我尝试将其转换为Dql。不幸的是，由于你们使用了括号，无法在Doctrine的聚合函数中使用。这是我的解决方法。现在它可以从grades表中计算所有A、B、C、D、F的成绩。学校班级（字母和数字）信息是从其父表中检索到的。A、B、C、D、F成绩并不完全是我想要的字段。就像下面的图片一样。

这可能有点棘手。

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

- Johny