I have a repository function and I want to use CASE WHEN THEN ELSE, but doctrine doesn't allow NULL in ELSE. Sure, i googled, but I couldn't find an answer how to avoid it.
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();
}
Error: [Syntax Error] line 0, col 900: Error: Unexpected 'NULL'
https://github.com/doctrine/doctrine2/issues/3160
doctrinebot commented on 22 May 2013
Issue was closed with resolution "Won't Fix"
Please, give me advice.
I managed to bypass Doctrines logical checks by passing in the null value as a parameter. It's a hack, but it seems to be the only thing that works,.
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With