DDC-2793: Subquery into FROM #3497

New Issue

admin · 2026-01-22T14:21:01+01:00

admin commented

2026-01-22 14:21:01 +01:00

Originally created by @doctrinebot on GitHub (Nov 14, 2013).

Originally assigned to: @beberlei on GitHub.

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?

Originally created by @doctrinebot on GitHub (Nov 14, 2013). Originally assigned to: @beberlei on GitHub. 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?

admin added the Improvement label 2026-01-22 14:21:01 +01:00

admin closed this issue

2026-01-22 14:21:01 +01:00

admin commented

2026-01-22 14:21:02 +01:00

@doctrinebot commented on GitHub (Nov 14, 2013):

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 commented on GitHub (Nov 14, 2013): 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 )`

admin commented

2026-01-22 14:21:02 +01:00

@doctrinebot commented on GitHub (Nov 14, 2013):

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 commented on GitHub (Nov 14, 2013): Comment created by pcastrotigre: What if my subquery is something like: ``` sql 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.

admin commented

2026-01-22 14:21:02 +01:00

@doctrinebot commented on GitHub (Nov 14, 2013):

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 commented on GitHub (Nov 14, 2013): 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)

admin commented

2026-01-22 14:21:02 +01:00

@doctrinebot commented on GitHub (Nov 14, 2013):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Nov 14, 2013): Issue was closed with resolution "Fixed"

admin commented

2026-01-22 14:21:05 +01:00

@Ocramius commented on GitHub (Aug 3, 2020):

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

@Ocramius commented on GitHub (Aug 3, 2020): If you are doing more complex queries, you should most likely use SQL directly anyway.

admin commented

2026-01-22 14:21:06 +01:00

@arthurlauck commented on GitHub (Oct 8, 2020):

Still not supporting subquery selects in from statement?

@arthurlauck commented on GitHub (Oct 8, 2020): Still not supporting subquery selects in from statement?

admin commented

2026-01-22 14:21:07 +01:00

@FluffyDiscord commented on GitHub (Jan 12, 2023):

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

there is nothing complex about such a simple query

@FluffyDiscord commented on GitHub (Jan 12, 2023): > If you are doing more complex queries, you should most likely use SQL directly anyway. there is nothing complex about such a simple query

admin commented

2026-01-22 14:21:07 +01:00

@hedocode commented on GitHub (Aug 25, 2023):

Non-sense when we know that Doctrine Paginator take a select with only entity id.
The use case is that you may want to select some more data to order by a calculus and then just subrequest to get entity's id only and still be a DQL query.

Same entity, still can do the mapping, idk why it's not supported.

SELECT toto.id FROM (
    SELECT t.id AS id, (
        (t.a / t.c)
    ) as percent
    FROM tata t 
) AS toto ORDER BY percent DESC;

@hedocode commented on GitHub (Aug 25, 2023): Non-sense when we know that Doctrine Paginator take a select with only entity id. The use case is that you may want to select some more data to order by a calculus and then just subrequest to get entity's id only and still be a DQL query. Same entity, still can do the mapping, idk why it's not supported. ``` SELECT toto.id FROM ( SELECT t.id AS id, ( (t.a / t.c) ) as percent FROM tata t ) AS toto ORDER BY percent DESC; ```

admin commented

2026-01-22 14:21:07 +01:00

@derrabus commented on GitHub (Aug 25, 2023):

Same entity, still can do the mapping, idk why it's not supported.

Go ahead, build that feature if it's that simple.

@derrabus commented on GitHub (Aug 25, 2023): > Same entity, still can do the mapping, idk why it's not supported. Go ahead, build that feature if it's that simple.

admin commented

2026-01-22 14:21:08 +01:00

@mpdude commented on GitHub (Aug 25, 2023):

I have recently been debugging some more complex queries with sub selects where MySQL 8 was way less performant than 5.7. So I wonder if this would help me 🙂

@mpdude commented on GitHub (Aug 25, 2023): I have recently been debugging some more complex queries with sub selects where MySQL 8 was way less performant than 5.7. So I wonder if this would help me 🙂

admin commented

2026-01-22 14:21:08 +01:00

@hedocode commented on GitHub (Sep 1, 2023):

Go ahead, build that feature if it's that simple.

Sorry if that felt offensive, didn't mean to be (mean), just wanted to understand why in some cases we can't do such things. I would be glad to if I had time tbh.

Anyway, if anyone is going through the same problem as I did, I solved it by putting the calculus in the order directly so I don't have to select it and then the paginator works

SELECT t.id
FROM tata t 
ORDER BY (
    (t.a / t.c)
)  DESC;

instead of :

SELECT toto.id FROM (
    SELECT t.id AS id, (
        (t.a / t.c)
    ) as percent
    FROM tata t 
) AS toto ORDER BY percent DESC;

@hedocode commented on GitHub (Sep 1, 2023): > Go ahead, build that feature if it's that simple. Sorry if that felt offensive, didn't mean to be (mean), just wanted to understand why in some cases we can't do such things. I would be glad to if I had time tbh. Anyway, if anyone is going through the same problem as I did, I solved it by putting the calculus in the order directly so I don't have to select it and then the paginator works ``` SELECT t.id FROM tata t ORDER BY ( (t.a / t.c) ) DESC; ``` instead of : > ``` > SELECT toto.id FROM ( > SELECT t.id AS id, ( > (t.a / t.c) > ) as percent > FROM tata t > ) AS toto ORDER BY percent DESC; > ```

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3497