CODEWITHSUNDEEP
mysqlsymfonydoctrine
13bonobo
13bonobo

Reputation: 91

SQL query with count and group by in Symfony2 QueryBuilder

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 :)

Upvotes: 9

Views: 35318

Answers (3)

wasbaiti
wasbaiti

Reputation: 51

$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

Upvotes: 2

gorodezkiy
gorodezkiy

Reputation: 3419

I tried to use 

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

and it's seem to work fine so far.

Upvotes: 4

Serhii Topolnytskyi
Serhii Topolnytskyi

Reputation: 820

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

Upvotes: 8

Related Questions