CODEWITHSUNDEEP
phpdoctrine-ormdql
Bruno Souza
Bruno Souza

Reputation: 218

How to do a WHERE CASE WHEN in doctrine's queryBuilder

i have the following function using query builder:

    public function showForJustWhoCanSee() {
        $nextToSign = $this->em->createQueryBuilder();
        $nextToSign->select('u')
            ->from('HospitalApi\Entity\EletronicDocumentSignature', 's')
            ->innerJoin('HospitalApi\Entity\User', 'u', 'WITH', 's.user = u')
            ->where('s._document = :documentId')
            ->setParameter('documentId', 'ed.id')
            ->andWhere('s.signed = 0')
            ->setMaxResults('1')
            ->orderBy('s.order', 'ASC');


        $subquery = $this->em->createQueryBuilder();
        $subquery->select('userSignature')
            ->from('HospitalApi\Entity\EletronicDocumentSignature', 'signature')
            ->innerJoin('HospitalApi\Entity\User', 'userSignature', 'WITH', 'userSignature = signature.user')
            //->where('signature.signed = 0')
            ->groupBy('signature._document')
            ->orderBy('signature.order', 'ASC');

        $select = $this->em->createQueryBuilder();
        $select->select('ed')
            ->from($this->getEntityPath(), 'ed')
            ->innerJoin("HospitalApi\Entity\User", "u", "with", "ed.user = u")
            ->leftJoin("HospitalApi\Entity\EletronicDocumentSignature", 'eds', 'WITH', 'eds._document = ed')
            ->leftJoin("eds.user", 'us', 'WITH', 'u = :user OR us = :user')
            ->where( $select->expr()->eq( 'us', $select->expr()->any( $subquery->getDQL() )) )
            //   ->Need a andWhere here<-
            ->orwhere('u = :user')
            ->setParameter( 'user', $this->getSession() )
            ->andWhere('ed.c_removed = 0');
        return $select;
    }

I would like to know how i can add a andWhere() clause to the $select query that does the following:

CASE WHEN 'ed.user' = ':user' THEN $nextToSign = ':user'

I have tried

->andWhere( 'CASE WHEN', $select->expr()->neq('ed.user', ':user'), 'THEN', '$nextToSign = :user' )

->andWhere( 'CASE', 'WHEN', $select->expr()->neq('ed.user', ':user'), 'THEN', $nextToSign = ':user' )

->andWhere( 'CASE', 'WHEN', $select->expr()->neq('ed.user', ':user'), 'THEN', $select->expr()->eq($nextToSign, ':user') )

->andWhere( "CASE", "WHEN", $select->expr()->neq("ed.user", ":user"), "THEN", $select->expr()->eq(":user", $nextToSign ), "END" )

and a few other variations

None of these work. Is there a way to make this kind of query with the query builder?

Upvotes: 2

Views: 5795

Answers (1)

Flying
Flying

Reputation: 4560

You need to have END to be added at the end of the statement.

Please refer EBNF notation for case statement into Doctrine documentation for exact syntax specification.

So probably your code needs to look like this:

->andWhere('CASE WHEN ed.user != :user THEN <expr> END')

I'm not sure what does your $select->expr()->eq($nextToSign, ':user') statement actually means taking in mind that your code example shows that $nextToSign is query builder. Hence I've replaced this part of your statement with <expr>.

Upvotes: 4

Related Questions