Doctrine: GROUP BY HAVING

doctrine

Solution

Your SQL is valid, Your query builder statement is invalid

All cause db will execute that query in following order:

 1. FROM      $query = $this->createQueryBuilder('q')
 2. GROUP BY  ->groupby('userId') // GROUP BY
 3. HAVING    ->having('counter >= 3')
 4. SELECT    ->select('userId, count(userId) as counter')

So as You can see `counter` is defined after its use in having.

Its SQL Quirk. You can not use definitions from select in `where` or `having` statements.

So correct code:

$query = $this->createQueryBuilder('q')
            ->select('userId, count(userId) as counter')
            ->groupby('userId')
            ->having('count(userId) >= 3')
            ->getQuery();

return $query->getResult();

Do note repetition in `having` from `select`

Problem

I'm trying to do this: ``` SELECT userId, count(userId) as counter FROM quicklink GROUP BY userId HAVING count(*) >= 3' ``` In doctrine with the querybuilder, I've got this: ``` $query = $this->createQueryBuilder('q') ->select('userId, count(userId) as counter') ->groupby('userId') ->having('counter >= 3') ->getQuery(); return $query->getResult(); ``` Which gives me this error: ``` [Semantical Error] line 0, col 103 near 'HAVING count(*)': Error: Cannot group by undefined identification variable. ``` Really struggling with doctrine. :(

Original source