Orm: DDC-2793: Subquery into FROM

[doctrinebot]

Jira issue originally created by user pcastrotigre:

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?

[doctrinebot]

Comment created by @ocramius:

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 )

[doctrinebot]

Comment created by pcastrotigre:

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.

[doctrinebot]

Comment created by stof:

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)

[doctrinebot]

Issue was closed with resolution "Fixed"

[Ocramius]

If you are doing more complex queries, you should most likely use SQL directly anyway.

[arthurlauck]

Still not supporting subquery selects in from statement?