[DDC-1236] GROUP BY does not work with functions Created: 29/Jun/11  Updated: 02/Dec/11  Resolved: 02/Dec/11

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.0-RC1
Fix Version/s: 2.2
Security Level: All

Type: Improvement Priority: Major
Reporter: Andreas Hörnicke Assignee: Guilherme Blanco
Resolution: Fixed Votes: 4
Labels: None


 Description   

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)


 Comments   
Comment by Benjamin Eberlei [ 29/Jun/11 ]

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

Marked as improvement.

Comment by Wladimir Coka [ 18/Oct/11 ]

Is there any workaround?

Comment by Andreas Hörnicke [ 18/Oct/11 ]

Using a native query is a workaround.

Comment by Benjamin Eberlei [ 18/Oct/11 ]

There is another workaround that should work:

SELECT ...,   CAST(t.scheduledDate as date) AS castedDate
FROM Task t
...
WHERE
  ...
GROUP BY
castedDate
Comment by Wladimir Coka [ 18/Oct/11 ]

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.

Comment by Benjamin Eberlei [ 18/Oct/11 ]

Then only native query is a workaround for now

We work on this for 2.2

Comment by Wladimir Coka [ 18/Oct/11 ]

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

Comment by Alex Barnes [ 03/Nov/11 ]

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

Comment by Guilherme Blanco [ 02/Dec/11 ]

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
Generated at Tue Sep 30 18:07:18 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.