Doctrine: GROUP BY HAVING

Question

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. :(

Answer 1

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

Answer 2

I am going to answer for people who still have this type of error.

First things first, you seem to have translated a native sql query inside a query builder object. More so, you have named your object as q

$query = $this->createQueryBuilder('q');

What this means, in general, is that every condition or grouping etc you have in your logic should address fields of q: q.userId, q.gender, ...

So, if you had written your code like below, you would have avoided your error:

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

return $query->getResult();

Answer 3

I think you are missing the 'from' statement