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