DDC-1236: GROUP BY does not work with functions

[doctrinebot]

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)

[doctrinebot]

Comment created by @beberlei:

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

Marked as improvement.

[doctrinebot]

Comment created by dimirc:

Is there any workaround?

[doctrinebot]

Comment created by h-andreas:

Using a native query is a workaround.

[doctrinebot]

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]

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]

Comment created by @beberlei:

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

We work on this for 2.2

[doctrinebot]

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]

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]

Comment created by @guilhermeblanco:

Since this commit: 2642daa
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]

Issue was closed with resolution "Fixed"

[diego-sorribas]

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?