CODEWITHSUNDEEP
mysqlsymfonydoctrine-ormdoctrinedql
Jacob Manser
Jacob Manser

Reputation: 122

In Doctrine QueryBuilder, trying to select records based on number of related entity records

Spent all day on this one so far...

I have entities Room and User. Many users to a room. That relationship is working great.

I'm trying to select all rooms that have fewer than four related users. Seems like it should be pretty straightforward with either DQL or QueryBuilder, but it's been giving me trouble. Couldn't find anything exactly fitting in searches. Most frequent unexpected results are either a) returning no results b) returning a single result that may or may not have <4 users.

Below is the code as I would have expected it to work. Any help would be appreciated, thank you!

$rooms_with_space = $em->createQueryBuilder()
    ->select('c')
    ->from('MyBundle:Room', 'c')
    ->leftJoin('c.users', 'u')
    ->having('COUNT(u.id) > 4')
    ->getQuery()
    ->getResult();

Upvotes: 1

Views: 987

Answers (1)

Sang Lu
Sang Lu

Reputation: 308

As I know, COUNT function on HAVING works when there is having a GROUP BY.

I think you need to add a GROUP BY of Room ID:

$rooms_with_space = $em->createQueryBuilder()
    ->select('c')
    ->from('MyBundle:Room', 'c')
    ->leftJoin('c.users', 'u')
    ->groupBy('c.id')
    ->having('COUNT(u.id) > 4')
    ->getQuery()
    ->getResult();

Hope this can help you!

Upvotes: 2

Related Questions