symfony - 原则 CASE WHEN THEN ELSE NULL

Question

我有一个存储库函数，我想使用 CASE WHEN THEN ELSE，但原则不允许 ELSE 中出现 NULL。当然，我用谷歌搜索过，但找不到如何避免它的答案。

public function countAverageOdd($user) {
    return $this->getEntityManager()
                    ->createQuery('
                            SELECT
                                avg(
                                    CASE
                                        WHEN p.homeAway = \'h\' AND p.moneyline IS NOT NULL THEN m.home
                                        WHEN p.homeAway = \'d\' AND p.moneyline IS NOT NULL THEN m.draw
                                        WHEN p.homeAway = \'e\' AND p.moneyline IS NOT NULL THEN m.away
                                        WHEN p.homeAway = \'h\' AND p.spread IS NOT NULL THEN s.home
                                        WHEN p.homeAway = \'e\' AND p.spread IS NOT NULL THEN s.away
                                        WHEN p.homeAway = \'h\' AND p.total IS NOT NULL THEN t.over
                                        WHEN p.homeAway = \'e\' AND p.total IS NOT NULL THEN t.under
                                    ELSE NULL
                                    END
                                    )
                            FROM
                                AppBundle:Predictions p
                                LEFT JOIN p.moneyline m
                                LEFT JOIN p.spread s
                                LEFT JOIN p.total t
                            WHERE p.user = :user ')
                    ->setParameter(":user", $user)
                    ->setMaxResults(1)
                    ->getSingleScalarResult();
}

错误:[语法错误]第 0 行，第 900 列:错误:意外的“NULL”

https://github.com/doctrine/doctrine2/issues/3160

doctrinebot 于 2013 年 5 月 22 日发表评论

问题已结束，决议为“无法修复”

请给我建议。

Answer 1

我通过传入空值作为参数设法绕过了 Doctrines 逻辑检查。这是一个 hack，但它似乎是唯一有效的方法。

public function countAverageOdd($user) {
    return $this->getEntityManager()
                    ->createQuery('
                            SELECT
                                avg(
                                    CASE
                                        WHEN p.homeAway = \'h\' AND p.moneyline IS NOT NULL THEN m.home
                                        WHEN p.homeAway = \'d\' AND p.moneyline IS NOT NULL THEN m.draw
                                        WHEN p.homeAway = \'e\' AND p.moneyline IS NOT NULL THEN m.away
                                        WHEN p.homeAway = \'h\' AND p.spread IS NOT NULL THEN s.home
                                        WHEN p.homeAway = \'e\' AND p.spread IS NOT NULL THEN s.away
                                        WHEN p.homeAway = \'h\' AND p.total IS NOT NULL THEN t.over
                                        WHEN p.homeAway = \'e\' AND p.total IS NOT NULL THEN t.under
                                    ELSE :null
                                    END
                                    )
                            FROM
                                AppBundle:Predictions p
                                LEFT JOIN p.moneyline m
                                LEFT JOIN p.spread s
                                LEFT JOIN p.total t
                            WHERE p.user = :user ')
                    ->setParameters(array(":user"=> $user,":null"=>NULL))
                    ->setMaxResults(1)
                    ->getSingleScalarResult();