user3746259 user3746259 - 1 year ago 322
PHP Question

Doctrine Query Builder Select Count with Case

I have the following sql-statement that I want to transform into doctrine query builder. The goal is to count how many ratings exist with rating value 1 and with rating value 2.

COUNT(CASE WHEN rating.rating = 1 THEN rating.rating END) as rat1,
COUNT(CASE WHEN rating.rating = 2 THEN rating.rating END) as rat2
FROM rating

This sql statement is working fine - but when I try to transform it into a Doctrine statement, it does not anymore. When nothing should get counted (because no ratings for this value exist), it returns me a "1" instead of a 0. How can I tell doctrine to simply return a zero when there is nothing to count?
I tried it by removing the "ELSE 0" , but then I get an error that this part is required..

return $qb
->addSelect('COUNT(CASE WHEN r.rating = 1 THEN r.rating ELSE 0 END) as rat_1')
->addSelect('COUNT(CASE WHEN r.rating = 2 THEN r.rating ELSE 0 END) as rat_2')


"sum" is not required - example:
votings 2,2,2,2,2 should return 5 , because the rating with value 2 got voted 5 times.

Answer Source

As vkp mentioned, you can use SUM but instead of summing the rating, sum either a 1 or a 0 to simulate a COUNT

SUM(CASE WHEN r.rating = 1 THEN 1 ELSE 0 END)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download