How to get COUNT of LEFT JOIN data

Question

I'm trying to get the ChannelGroups where the ChannelGroup has a count of 0 on its relation ChannelGroupUsers.

public function userEmptyChannelGroups()
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT cg
                 FROM App:ChannelGroup cg
                 LEFT JOIN cg.channelGroupUsers cgu
                 WHERE COUNT(cgu) = 0'
            )
            ->getArrayResult();
    }

But I keep getting SQLSTATE[HY000]: General error: 1111 Invalid use of group function (500 Internal Server Error) upon trying the above.

What am I doing wrong?

Probably learn how to use `GROUP BY`. – Eric May 30 '19 at 16:01 — Eric, May 30 '19 at 16:01

score 0 · Answer 1 · answered May 30 '19 at 15:49

If you want to find rows that don't have a connection to another table you can do something like this. In where there can be any column from table2.

select
    *
from table1 
left join table2
    on table1.id = table2.id
where table2.someColumn is null

score 0 · Accepted Answer · answered May 30 '19 at 15:50

You can't use an aggregate function, like COUNT in a WHERE clause. You can, however, use an aggregate function in a HAVING clause. See this question for more on the difference between WHERE and HAVING.

However, if you're just trying to get the rows in cg that don't have any joined rows from cgu, you can select where cgu.id is null.

For example:

SELECT cg
FROM App:ChannelGroup cg
LEFT JOIN cg.channelGroupUsers cgu
WHERE cgu.id IS NULL

How to get COUNT of LEFT JOIN data

2 Answers2