I'm trying to do this:

     userId, count(userId) as counter 
     count(*) >= 3'

In doctrine with the querybuilder, I've got this:

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

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


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')
            ->having('count(userId) >= 3')

return $query->getResult();

Do note repetition in having from select

I think you are missing the 'from' statement

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')
            ->having('counter >= 3')

return $query->getResult();

$query = "t, count(t.userId) as counter FROM YourBundle:Table t group by t.userId having counter >1 ";
return $this->getEntityManager()->createQuery($query)->getResult();

This should work. You can even do left joins or apply where clausules.

you can define HAVING parameter via setParameter() method as well

 $qb->having('COUNT(*) = :some_count');
 $qb->setParameter('some_count', 3);

Need Your Help

HTML5 audio/video doesn't work using fancybox overlay on iOS

jquery fancybox

When I embed an HTML5 audio/video tag into the fancybox overlay, the audio/video player doesn't display using any iOS device (iPhone/iPad). When calling open_audio_overlay() you can use this MP3 h...

Enter Key Does not Work on Key Listner


I'm using OnKeyListner to Address an 'Enter' key pressed via soft Keyboard on an edit text.