Doctrine 1.2: how to get the count of related table rows ina a query?

Question

I have a table of users, plus a related table called UserWorkplaces, where one user could have any number of related rows on the UserWorkplaces table.

I'm trying to build a query to find all of the users with only one related row in UserWorkplaces. What kind of query should I use to get this?

Here's what I've tried so far, but it gave me the count of everyone's workplaces and only a single user:

$query = Doctrine_Query::create()
    ->from('Users', 'u')
    ->innerJoin('u.userWorkplaces uw')
    ->having('COUNT(uw.id) = 1');

score 1 · Answer 1 · answered Jun 06 '13 at 08:11

I believe your problem is not Doctrine but the SQL you wanna achieve.

Make the story short, following code should work you.

$query = Doctrine_Query::create()
    ->from('Users u')
    ->innerJoin('u.userWorkplaces uw')
    ->having('COUNT(uw.id) = ?', 1)
    ->groupBy('u.id');

COUNT() is aggregate function and therefore you need to specify the GROUP BY parameter for the main table you want to list in.

Doctrine 1.2: how to get the count of related table rows ina a query?

1 Answers1