php - Struggling With DQL and Case When Expression

483

I am attempting the following query and am having some trouble with the count(CASE WHEN r.status=2 THEN 1 ELSE 0 END) part.

I really need to capture the count of status's where they are equal to "complete". I tried the following but I keep getting: Expected Doctrine\ORM\Query\Lexer:T_FROM, got '.'

can someone help?

$em = $this->getDoctrine()->getManager();
            $query = $em->createQuery(
                            'SELECT count(r.rpId) AS referralCount, count(CASE WHEN r.status=2 THEN 1 ELSE 0 END) as referralCompleted, p.firstName, p.lastName, p.npi, u.username
        FROM MainReferralCaptureBundle:Referral r, MainReferralCaptureBundle:Physician p, MainUserBundle:User u
        WHERE r.valid = 1 
        AND r.rpId = p.id
        AND r.submittedBy = u.id
        AND r.createdDate BETWEEN :startdate AND :enddate
        GROUP BY p.id')
                    ->setParameter('startdate', $form->get('startdate')->getData())
                    ->setParameter('enddate',  $form->get('enddate')->getData());
59

Answer

Solution:

You're not grouping by all non-aggregate fields from yourSELECT statement. That doesn't cause an error in some rdbms but it does cause results to be rubbish.

Additionally:

count(CASE WHEN r.status=2 THEN 1 ELSE 0 END)

Will count all records due to theELSE 0. Remove theELSE altogether and it will only count when the condition is met. Likewise you could change theELSE 0 toELSE NULL.NULL is excluded from aggregates. You could also changeCOUNT() toSUM().

That's all I notice from the query itself, not sure what the error you're getting is about.

People are also looking for solutions to the problem: php - ajax file upload fails only with SSL terminating load balancer

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.