Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:

      Description

      I am trying to do something like:

       
      SELECT count(A.*) FROM (SELECT u FROM Acme\Bundle\Entity\User u ) A
      

      This is not exactly the query i need, but that is the idea. The subquery into the FROM is not supported by the Query Builder neither the DQL because I am getting Error: Class '(' is not defined).

      So how could I do this?

        Activity

        Hide
        Marco Pivetta added a comment -

        This is currently not supported by DQL. The workaround right now is to use:

        SELECT count(A.*) FROM Acme\Bundle\Entity\User A WHERE A.id IN(SELECT u FROM Acme\Bundle\Entity\User u )
        Show
        Marco Pivetta added a comment - This is currently not supported by DQL. The workaround right now is to use: SELECT count(A.*) FROM Acme\Bundle\Entity\User A WHERE A.id IN(SELECT u FROM Acme\Bundle\Entity\User u )
        Hide
        Petter Castro added a comment -

        What if my subquery is something like:

        SELECT count(A.*) FROM (
          SELECT u.*,CUSTOM_SUM_FUNC(u.col1,u.col2) as my_value FROM Acme\Bundle\Entity\User u HAVING my_value > 5
        ) A
        

        In this case I have a HAVING clause, so I must return more than 1 column in the subquery, and the IN clause cannot be used.

        Show
        Petter Castro added a comment - What if my subquery is something like: SELECT count(A.*) FROM ( SELECT u.*,CUSTOM_SUM_FUNC(u.col1,u.col2) as my_value FROM Acme\Bundle\Entity\User u HAVING my_value > 5 ) A In this case I have a HAVING clause, so I must return more than 1 column in the subquery, and the IN clause cannot be used.
        Hide
        Christophe Coevoet added a comment -

        DQL is about querying objects. Supporting subselects in the FROM clause means that the DQL parser is not able to build the result set mapping anymore (as the fields returned by the subquery may not match the object anymore).
        This is why it cannot be supported (supporting it only for the case you run the query without the hydration is a no-go IMO as it would mean that the query parsing needs to be dependant of the execution mode).

        In your case, the best solution is probably to run a SQL query instead (as you are getting a scalar, you don't need the ORM hydration anyway)

        Show
        Christophe Coevoet added a comment - DQL is about querying objects. Supporting subselects in the FROM clause means that the DQL parser is not able to build the result set mapping anymore (as the fields returned by the subquery may not match the object anymore). This is why it cannot be supported (supporting it only for the case you run the query without the hydration is a no-go IMO as it would mean that the query parsing needs to be dependant of the execution mode). In your case, the best solution is probably to run a SQL query instead (as you are getting a scalar, you don't need the ORM hydration anyway)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Petter Castro
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: