Count and group SQL query in Symfony2 QueryBuilder

Question

I need your help, please. I have this SQL query:

SELECT * , COUNT( * ) AS count FROM mytable GROUP BY email ORDER BY id DESC LIMIT 0 , 30 

But I would like to do this in Symfony2 with Doctrine and createQueryBuilder (). I tried this but did not work:

 $db = $this->createQueryBuilder('s'); $db->andWhere('COUNT( * ) AS count'); $db->groupBy('s.email'); $db->orderBy('s.id', 'DESC'); 

Can you help me? Thanks:)

Answer 1

You need to run 2 queries:

 $db = $this->createQueryBuilder(); $db ->select('s') ->groupBy('s.email') ->orderBy('s.id', 'DESC') ->setMaxResults(30); $qb->getQuery()->getResult(); 

and

 $db = $this->createQueryBuilder(); $db ->select('count(s)') ->groupBy('s.email') //->orderBy('s.id', 'DESC') ->setMaxResults(1); $qb->getQuery()->getSingleScalarResult(); 

Answer 2

 $db = $this->createQueryBuilder('mytable'); $db ->addSelect('COUNT(*) as count') ->groupBy('mytable.email') ->orderBy('mytable.id', 'DESC') ->setFirstResult(0) ->setMaxResults(30); $result = $db->getQuery()->getResult(); 

Hope this helps, even if it's an old stack

Answer 3

I tried to use

 $query->select('COUNT(DISTINCT u)'); 

and it looks like it is working fine so far.