Doctrine: GROUP BY HAVING

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

Answers


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


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')
            ->groupby('q.userId')
            ->having('counter >= 3')
            ->getQuery();

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->groupBy('userId');
 $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

android

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