学说 案例 当否则为空

Question

我有一个存储库函数，我想在 ELSE 中使用 CASE，但学说不允许在 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

教义机器人于2013年5月22日发表评论

问题已关闭，解决方案"无法修复">

请给我建议。

Answer 1

我设法通过将 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 ')
                    ->setParameters(array(":user"=> $user,":null"=>NULL))
                    ->setMaxResults(1)
                    ->getSingleScalarResult();

Answer 2

另一个技巧是使用NULLIF，这是支持的。例如

CASE WHEN WHEN p.homeAway = 'h' THEN 'xyz' ELSE NULLIF(1,1) END

从 https://github.com/doctrine/orm/issues/5801#issuecomment-336132280 那里得到这个

谢谢！

Answer 3

查看

myql case运算符文档。ELSE是可选的，因此您可以将其删除

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