Doctrine- Group by date

Question

I have this query :

SELECT DATE_FORMAT(exp_date, "%Y-%m") AS Month, sum(exp_total) FROM export
GROUP BY DATE_FORMAT(exp_date, "%Y-%m");

I tried to convert it to Symfony doctrine like below :

$qb = $this
  ->createQueryBuilder('e')
  ->select('DATE_FORMAT(e.expDate, \'%Y-%m\'), sum(e.expTotal) total')
  ->groupBy('DATE_FORMAT(e.expDate, \'%Y-%m\')');
return $qb->getQuery()->getResult();

Using:

"beberlei/DoctrineExtensions": "^1.0"

Error: [Semantical Error] line 0, col 103 near 'DATE_FORMAT(e.expDate,': Error: Cannot group by undefined identification or result variable.

what is the type of your expDate, is it Date or DateTime? – habibun Jan 01 '18 at 03:25 — habibun, Jan 01 '18 at 03:25

score 8 · Accepted Answer · answered Jan 01 '18 at 07:06

8

I assume you have configured the mentioned bundle and added the required configuration like

doctrine:
    orm:
        dql:
            datetime_functions:
                date_format: DoctrineExtensions\Query\Mysql\DateFormat

Now in query you can assign an alias as dateAsMonth to the result of DATE_FORMAT expression and in group by you can use this alias

return $qb =   $this->createQueryBuilder('e')
                    ->select('DATE_FORMAT(e.expDate, \'%Y-%m\') as dateAsMonth, sum(e.expTotal) total')
                    ->groupBy('dateAsMonth')
                    ->getQuery()
                    ->getResult();

answered Jan 01 '18 at 07:06

M Khalid Junaid

63,861
10
90
118

Unrecognized option "dql" under "doctrine.orm" in doctrine/orm 2.5. – felansu Dec 12 '18 at 11:15
1

@felansu you probably forgot to set up doctrine extensions as described in the link he provided (configuration) – Miles M. Jan 05 '19 at 18:04

Doctrine- Group by date

1 Answers1

Linked