SQL query with count and group by 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 the createQueryBuilder(). I try this, but didn't work :

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

Can you help me please ? Thanks :)

what do you mean with didn't work? How to LIMIT your Doctrine results was answered here before — ferdynator
– ferdynator, Commented Jul 14, 2013 at 19:38

Serhii Topolnytskyi · Accepted Answer · 2014-02-17 14:49:49Z

8

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();

answered Feb 17, 2014 at 14:49

Serhii Topolnytskyi

8307 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FlameStorm Over a year ago

Wrong answer, it leads not to the same result as in initial query in the question.

gorodezkiy · Accepted Answer · 2015-06-12 21:21:22Z

4

I tried to use

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

and it's seem to work fine so far.

answered Jun 12, 2015 at 21:21

gorodezkiy

3,4972 gold badges37 silver badges44 bronze badges

Comments

Raghav Rach · Accepted Answer · 2017-06-08 14:46:33Z

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 it helps even if it's an old stack

edited Jun 8, 2017 at 14:46

Raghav Rach

4,9452 gold badges18 silver badges16 bronze badges

answered Mar 15, 2017 at 14:24

wasbaiti

514 bronze badges

Collectives™ on Stack Overflow

SQL query with count and group by in Symfony2 QueryBuilder

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related