Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1236

GROUP BY does not work with functions

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0-RC1
    • Fix Version/s: 2.2
    • Component/s: DQL
    • Security Level: All
    • 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)
      

        Activity

        Andreas Hörnicke created issue -
        Hide
        Benjamin Eberlei added a comment -

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

        Marked as improvement.

        Show
        Benjamin Eberlei added a comment - This is documented in the EBNF of DQL that this not works. Marked as improvement.
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Issue Type Bug [ 1 ] Improvement [ 4 ]
        Fix Version/s 2.x [ 10090 ]
        Hide
        Wladimir Coka added a comment -

        Is there any workaround?

        Show
        Wladimir Coka added a comment - Is there any workaround?
        Hide
        Andreas Hörnicke added a comment -

        Using a native query is a workaround.

        Show
        Andreas Hörnicke added a comment - Using a native query is a workaround.
        Hide
        Benjamin Eberlei added a comment -

        There is another workaround that should work:

        SELECT ...,   CAST(t.scheduledDate as date) AS castedDate
        FROM Task t
        ...
        WHERE
          ...
        GROUP BY
        castedDate
        
        Show
        Benjamin Eberlei added a comment - There is another workaround that should work: SELECT ..., CAST(t.scheduledDate as date) AS castedDate FROM Task t ... WHERE ... GROUP BY castedDate
        Hide
        Wladimir Coka added a comment - - edited

        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.

        Show
        Wladimir Coka added a comment - - edited 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.
        Hide
        Benjamin Eberlei added a comment -

        Then only native query is a workaround for now

        We work on this for 2.2

        Show
        Benjamin Eberlei added a comment - Then only native query is a workaround for now We work on this for 2.2
        Hide
        Wladimir Coka added a comment -

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

        Show
        Wladimir Coka added a comment - Ok I'll need to move my code to native query... Hope you can fix this with 2.2 or earlier ... Thanks
        Benjamin Eberlei made changes -
        Fix Version/s 2.2-DEV [ 10157 ]
        Fix Version/s 2.x [ 10090 ]
        Hide
        Alex Barnes added a comment - - edited

        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

        Show
        Alex Barnes added a comment - - edited 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
        Hide
        Guilherme Blanco added a comment -

        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
        
        Show
        Guilherme Blanco added a comment - 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
        Guilherme Blanco made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Assignee Benjamin Eberlei [ beberlei ] Guilherme Blanco [ guilhermeblanco ]
        Resolution Fixed [ 1 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 12760 ] jira-feedback [ 14923 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 14923 ] jira-feedback2 [ 16787 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 16787 ] jira-feedback3 [ 19040 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-1236, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Andreas Hörnicke
          • Votes:
            4 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: