orm: DDC-1236: GROUP BY does not work with functions

Jira issue originally created by user h-andreas:

The following query fails with Cannot group by undefined identification variable (yes CAST-Function was added). Would be nice to have such a feature,

SELECT ...
FROM Task t
...
WHERE
  ...
GROUP BY
  CAST(t.scheduledDate as date)

About this issue

Original URL
State: closed
Created 13 years ago
Comments: 17 (2 by maintainers)

Most upvoted comments

My DQL: SELECT u, FIRST(SELECT SUM(ap3.sum) as sum1, DATE(ap3.date) AS HIDDEN date1 FROM App\Entity\Payment ap3 WHERE ap3.user = u.id GROUP BY date1 ORDER BY sum1 DESC) pmd FROM App\Entity\User u ORDER BY u.id DESC [FIRST (adds LIMIT 1 in SQL) and DATE functions were added] Throws QueryException: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ‘,’ Which says about this comma ..sum1, DATE...

RoDmitry on Aug 28, 2019

@diego-sorribas it does work, you first have to register a custom DQL function to map to the SQL one and use the hidden field as explained in the other comments

lcobucci on Jun 14, 2019

Comment created by @guilhermeblanco:

Since this commit: https://github.com/doctrine/doctrine2/commit/2642daa43851878688d01625f272ff5874cac7b2 This issue is solved through the SelectExpression Hidden ResultVariable refer.

SELECT 
    ..., CAST(t.scheduledDate as date) AS HIDDEN groupDate
FROM Task t
    ...
WHERE
    ...
GROUP BY
    groupDate

doctrinebot on Dec 6, 2015