Doctrine - filter by foreign agregate value

Question

Hi there, how can i use result of Agregate function in where in Doctrine?

For example i want to know user with silly much numbers.

SELECT  u.name, COUNT(p.id) AS users_phonenumber_count
FROM    users u

    INNER JOIN phonenumbers p ON p.user_id = u.id
WHERE
    users_phonenumber_count > 10
GROUP BY
    u.id

How can i access the *users_phonenumber_count* in where in Dql?

Answer 1

I can't remember exact details of how I've done this, but remember that it had to use "HAVING"

This is because of the way SQL works, not specifically doctrine - WHERE can't compare computed values, you have to use having (or you could do WHERE COUNT(something) < something )

Answer 2

You need to using HAVING, not WHERE. Also, you need to group by something that's actually in your query, in this case u.name. Assuming u.name is unique - if not, you need to group by both u.id and u.name.

SELECT  u.name, COUNT(*) AS users_phonenumber_count
FROM    users u

    INNER JOIN phonenumbers p ON p.user_id = u.id
GROUP BY
    u.name
HAVING
    count(*) > 10