DDC-1236: GROUP BY does not work with functions #1554

New Issue

admin · 2026-01-22T13:17:54+01:00

admin commented

2026-01-22 13:17:54 +01:00

Originally created by @doctrinebot on GitHub (Jun 29, 2011).

Originally assigned to: @guilhermeblanco on GitHub.

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)

Originally created by @doctrinebot on GitHub (Jun 29, 2011). Originally assigned to: @guilhermeblanco on GitHub. 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, ``` sql SELECT ... FROM Task t ... WHERE ... GROUP BY CAST(t.scheduledDate as date) ```

admin added the Improvement label 2026-01-22 13:17:54 +01:00

admin closed this issue

2026-01-22 13:17:56 +01:00

admin commented

2026-01-22 13:17:57 +01:00

@doctrinebot commented on GitHub (Jun 29, 2011):

Comment created by @beberlei:

This is documented in the EBNF of DQL that this not works.

Marked as improvement.

@doctrinebot commented on GitHub (Jun 29, 2011): Comment created by @beberlei: This is documented in the EBNF of DQL that this not works. Marked as improvement.

admin commented

2026-01-22 13:17:58 +01:00

@doctrinebot commented on GitHub (Oct 18, 2011):

Comment created by dimirc:

Is there any workaround?

@doctrinebot commented on GitHub (Oct 18, 2011): Comment created by dimirc: Is there any workaround?

admin commented

2026-01-22 13:17:59 +01:00

@doctrinebot commented on GitHub (Oct 18, 2011):

Comment created by h-andreas:

Using a native query is a workaround.

@doctrinebot commented on GitHub (Oct 18, 2011): Comment created by h-andreas: Using a native query is a workaround.

admin commented

2026-01-22 13:17:59 +01:00

@doctrinebot commented on GitHub (Oct 18, 2011):

Comment created by @beberlei:

There is another workaround that should work:

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

@doctrinebot commented on GitHub (Oct 18, 2011): Comment created by @beberlei: There is another workaround that should work: ``` SELECT ..., CAST(t.scheduledDate as date) AS castedDate FROM Task t ... WHERE ... GROUP BY castedDate ```

admin commented

2026-01-22 13:18:00 +01:00

@doctrinebot commented on GitHub (Oct 18, 2011):

Comment created by dimirc:

I tried this:

SELECT ... ,MONTH(t.fechaCreacion) as mes FROM Tramite t ... WHERE ... GROUP BY mes

But I get this error:

Message: [Semantical Error] line 0, col 202 near 'mes ': Error: 'mes' does not point to a Class.

@doctrinebot commented on GitHub (Oct 18, 2011): Comment created by dimirc: I tried this: `SELECT ... ,MONTH(t.fechaCreacion) as mes FROM Tramite t ... WHERE ... GROUP BY mes` But I get this error: Message: [Semantical Error] line 0, col 202 near 'mes ': Error: 'mes' does not point to a Class.

admin commented

2026-01-22 13:18:01 +01:00

@doctrinebot commented on GitHub (Oct 18, 2011):

Comment created by @beberlei:

Then only native query is a workaround for now :-(

We work on this for 2.2

@doctrinebot commented on GitHub (Oct 18, 2011): Comment created by @beberlei: Then only native query is a workaround for now :-( We work on this for 2.2

admin commented

2026-01-22 13:18:02 +01:00

@doctrinebot commented on GitHub (Oct 18, 2011):

Comment created by dimirc:

Ok I'll need to move my code to native query... Hope you can fix this with 2.2 or earlier (y) ... Thanks

@doctrinebot commented on GitHub (Oct 18, 2011): Comment created by dimirc: Ok I'll need to move my code to native query... Hope you can fix this with 2.2 or earlier (y) ... Thanks

admin commented

2026-01-22 13:18:03 +01:00

@doctrinebot commented on GitHub (Nov 3, 2011):

Comment created by alexb:

BTW I have put in a fix and so far have had no issues.... but this should be considered hack as I'm not very familiar with the internal workings of Doctrine.

See doctrine-dev groups:
https://groups.google.com/d/topic/doctrine-dev/QZMMnYV4V_8/discussion

HTH

Alex

@doctrinebot commented on GitHub (Nov 3, 2011): Comment created by alexb: BTW I have put in a fix and so far have had no issues.... but this should be considered hack as I'm not very familiar with the internal workings of Doctrine. See doctrine-dev groups: https://groups.google.com/d/topic/doctrine-dev/QZMMnYV4V_8/discussion HTH Alex

admin commented

2026-01-22 13:18:03 +01:00

@doctrinebot commented on GitHub (Dec 2, 2011):

Comment created by @guilhermeblanco:

Since this commit: 2642daa438
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 commented on GitHub (Dec 2, 2011): Comment created by @guilhermeblanco: Since this commit: https://github.com/doctrine/doctrine2/commit/2642daa43851878688d01625f272ff5874cac7b2 This issue is solved through the SelectExpression <ins> Hidden </ins> ResultVariable refer. ``` SELECT ..., CAST(t.scheduledDate as date) AS HIDDEN groupDate FROM Task t ... WHERE ... GROUP BY groupDate ```

admin commented

2026-01-22 13:18:05 +01:00

@doctrinebot commented on GitHub (Dec 2, 2011):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Dec 2, 2011): Issue was closed with resolution "Fixed"

admin commented

2026-01-22 13:18:05 +01:00

@diego-sorribas commented on GitHub (Jun 12, 2019):

Hello, If I create a custom sql function like DATEPART for Sql Server, IF I want to group by the function its doesnt works, is that ok?

@diego-sorribas commented on GitHub (Jun 12, 2019): Hello, If I create a custom sql function like DATEPART for Sql Server, IF I want to group by the function its doesnt works, is that ok?

admin commented

2026-01-22 13:18:05 +01:00

@lcobucci commented on GitHub (Jun 14, 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 commented on GitHub (Jun 14, 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

admin commented

2026-01-22 13:18:07 +01:00

@diego-sorribas commented on GitHub (Jun 14, 2019):

I didn't see that, but that alias works on sql server?

@diego-sorribas commented on GitHub (Jun 14, 2019): I didn't see that, but that alias works on sql server?

admin commented

2026-01-22 13:18:07 +01:00

@lcobucci commented on GitHub (Jun 15, 2019):

https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html#adding-your-own-functions-to-the-dql-language explains how to register custom DQL functions

@lcobucci commented on GitHub (Jun 15, 2019): https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html#adding-your-own-functions-to-the-dql-language explains how to register custom DQL functions

admin commented

2026-01-22 13:18:07 +01:00

@RoDmitry commented on GitHub (Jun 21, 2019):

Hi.
I'm trying to group by function DATE(t.datetime). Function was added. But i'm using it in subquery $queryBuilder->addSelect('(' . $subqueryBuilder->getDQL() . ') pmd'); which is "simpleselect" (as I understood) and allows only one field in select. How can I group by this function in subquery?
I guess simpleselect must support HIDDEN fields.

@RoDmitry commented on GitHub (Jun 21, 2019): Hi. I'm trying to group by function `DATE(t.datetime)`. Function was added. But i'm using it in subquery `$queryBuilder->addSelect('(' . $subqueryBuilder->getDQL() . ') pmd');` which is "simpleselect" (as I understood) and allows only one field in select. How can I group by this function in subquery? I guess simpleselect must support HIDDEN fields.

admin commented

2026-01-22 13:18:07 +01:00

@RoDmitry commented on GitHub (Jun 21, 2019):

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 commented on GitHub (Jun 21, 2019): 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..`.

admin commented

2026-01-22 13:18:07 +01:00

@ax1mx2 commented on GitHub (Jun 8, 2020):

@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

I does not work, since MSSQL server and Oracle do not allow for aliases in the GROUP BY clause.

@ax1mx2 commented on GitHub (Jun 8, 2020): > @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 I does not work, since MSSQL server and Oracle do not allow for aliases in the GROUP BY clause.

admin referenced this issue

2026-01-22 16:04:52 +01:00

[PR #1554] Fix a documentation error in the EntityManagerInterface#transactional() return value #9615

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#1554