Unable to sort DQL Query Results by a value computed by a SubQuery #7302

New Issue

2026-01-22T15:49:34+01:00

admin commented

2026-01-22 15:49:34 +01:00

Originally created by @eXsio on GitHub (Jan 23, 2024).

Bug Report

I want to select a custom DTO that contains, as one of its fields, a value computed by a SubQuery that is defined in the Selection List. Furthermore, I need to sort the Result by the value computed by that SubQuery.

Q	A
BC Break	no
Version	2.17.x

Summary

There is currently no way (known to me) to sort a Criteria Query result by a value computed by a SubQuery. None of the things I've tried works:

adding an alias to a SubQuery and using it in the orderBy() method
putting the Subquery directly into the orderBy() method
ordering by column number (which is also a well defined SQL standard)

Current behavior

All attempts end up with some kind of Doctrine Parser error:

Unable to assign an alias to a SubQuery: [Syntax Error] line 0, col 178: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'as'
Unable to put SubQuery directly into the orderBy() method: [Syntax Error] line 0, col 247: Error: Expected Doctrine\ORM\Query\Lexer::T_IDENTIFIER, got '('
Unable to sort by the column number (directly): [Syntax Error] line 0, col 247: Error: Expected Doctrine\ORM\Query\Lexer::T_IDENTIFIER, got '3'
Unable to sort by the column number (via parameter): [Syntax Error] line 0, col 247: Error: Expected Doctrine\ORM\Query\Lexer::T_IDENTIFIER, got ':columnNumber'

How to reproduce

A full reproduction will all methods mentioned in this Ticket is here.

Expected behavior

At least one of the proposed ways should work, or there should be another way to sort the Criteria Query Result by the result of the SubQuery.

Originally created by @eXsio on GitHub (Jan 23, 2024). ### Bug Report I want to select a custom DTO that contains, as one of its fields, a value computed by a SubQuery that is defined in the Selection List. Furthermore, I need to sort the Result by the value computed by that SubQuery. | Q | A |------------ | ------ | BC Break | no | Version | 2.17.x #### Summary There is currently no way (known to me) to sort a Criteria Query result by a value computed by a SubQuery. None of the things I've tried works: - adding an alias to a SubQuery and using it in the `orderBy()` method - putting the Subquery directly into the `orderBy()` method - ordering by column number (which is also a well defined SQL standard) #### Current behavior All attempts end up with some kind of Doctrine Parser error: - Unable to assign an alias to a SubQuery: `[Syntax Error] line 0, col 178: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'as'` - Unable to put SubQuery directly into the orderBy() method: `[Syntax Error] line 0, col 247: Error: Expected Doctrine\ORM\Query\Lexer::T_IDENTIFIER, got '('` - Unable to sort by the column number (directly): `[Syntax Error] line 0, col 247: Error: Expected Doctrine\ORM\Query\Lexer::T_IDENTIFIER, got '3'` - Unable to sort by the column number (via parameter): `[Syntax Error] line 0, col 247: Error: Expected Doctrine\ORM\Query\Lexer::T_IDENTIFIER, got ':columnNumber'` #### How to reproduce A full reproduction will all methods mentioned in this Ticket is [here](https://github.com/eXsio/doctrine-orm/blob/2.17.x/tests/Doctrine/Tests/ORM/Functional/Ticket/GHXXXXXTest.php). #### Expected behavior At least one of the proposed ways should work, or there should be another way to sort the Criteria Query Result by the result of the SubQuery.

admin commented

2026-01-22 15:49:34 +01:00

@eXsio commented on GitHub (Jan 25, 2024):

Can I get some feedback on this? Is this a bug or rather a missing feature? To me it looks more like a bug (or at least a big inconsistency), because we can add aliases to other things like columns or even complex expressions. I don't see any reason for not being able to do the same for SubQueries. The lack of such possibility severely cripples the entire feature of having SubQueries in the Selection List.

@eXsio commented on GitHub (Jan 25, 2024): Can I get some feedback on this? Is this a bug or rather a missing feature? To me it looks more like a bug (or at least a big inconsistency), because we can add aliases to other things like columns or even complex expressions. I don't see any reason for not being able to do the same for SubQueries. The lack of such possibility severely cripples the entire feature of having SubQueries in the Selection List.

admin commented

2026-01-22 15:49:35 +01:00

@pich commented on GitHub (Aug 29, 2024):

👀 IMHO it's a bug

@pich commented on GitHub (Aug 29, 2024): 👀 IMHO it's a bug

admin commented

2026-01-22 15:49:36 +01:00

@domino91 commented on GitHub (Aug 29, 2024):

yep, that's a problem

@domino91 commented on GitHub (Aug 29, 2024): yep, that's a problem

admin commented

2026-01-22 15:49:36 +01:00

@tomasvts commented on GitHub (Oct 22, 2024):

I recently came across this issue, and for anyone looking for a workaround, it can be resolved using a table subquery:

        $qb1 = $this->connection->createQueryBuilder()
            ->select('temp.*')
            ->from("(" . $qb->getSQL() . ")", 'temp')
            ->setParameter('requesterId', $contractRequester)
            ->setParameter('contractId', $contractId)
            ->orderBy('totalSpent', 'DESC');

@tomasvts commented on GitHub (Oct 22, 2024): I recently came across this issue, and for anyone looking for a workaround, it can be resolved using a table subquery: ```php $qb1 = $this->connection->createQueryBuilder() ->select('temp.*') ->from("(" . $qb->getSQL() . ")", 'temp') ->setParameter('requesterId', $contractRequester) ->setParameter('contractId', $contractId) ->orderBy('totalSpent', 'DESC'); ```

admin referenced this issue

2026-01-22 16:07:27 +01:00

[PR #7302] Fix: Attributes vs. annotations #10414

admin referenced this issue

2026-01-22 16:09:56 +01:00

[PR #8790] Backport docs batch #11173

Sign in to join this conversation.