vendredi 31 juillet 2015

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.

SELECT 
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')
        ->getQuery()
        ->getResult();

Regards,

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

Aucun commentaire:

Enregistrer un commentaire