Doctrine2 - need some help modifying working dql

Question

I have the following dql SELECT query, which works:

SELECT q AS question, AVG(r.score) AS average
  FROM MyBundle:Question q
  JOIN q.ratings r
  WHERE q.deleted IS NULL
  GROUP BY q.id
  ORDER BY q.id ASC

What I'd like to do is make it return 0 if AVG(r.score) is either 0 or null. I'm just not sure how to do it. Is it possible to do something like:

SELECT q AS question, (AVG(r.score) OR 0) AS average FROM....

Answer 1

Since you can't use aggregate function in a WHERE clause, you must use HAVING instead. That should do the trick:

SELECT q AS question, AVG(r.score) AS average
  FROM MyBundle:Question q
  JOIN q.ratings r
  WHERE q.deleted IS NULL
  GROUP BY q.id
  HAVING average IN (0, NULL)
  ORDER BY q.id ASC

Answer 2

Are you looking for an alternative of mysql IFNULL to a doctrine query builder. Look at this question.

Answer 3

Changing my (INNER) JOIN to a LEFT OUTER JOIN gave me the results I was looking for.