CODEWITHSUNDEEP
symfonydoctrine-orm
NTsvetkov
NTsvetkov

Reputation: 117

Doctrine CASE WHEN THEN ELSE NULL

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.

Upvotes: 7

Views: 6364

Answers (3)

Azhar Khattak
Azhar Khattak

Reputation: 875

Another hack is to use NULLIF,which is supported. e.g

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

Got this from https://github.com/doctrine/orm/issues/5801#issuecomment-336132280

Thanks!

Upvotes: 6

freexe
freexe

Reputation: 116

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

Upvotes: 6

lordrhodos
lordrhodos

Reputation: 2745

checkout out the myql case operator documentation. The ELSE is optional, so you can just remove 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

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

Upvotes: -1

Related Questions