Arithmetic Operations in OrderBy with Aliases #7428

New Issue

admin · 2026-01-22T15:51:37+01:00

admin commented

2026-01-22 15:51:37 +01:00

Originally created by @danielbattat on GitHub (Oct 9, 2024).

Bug Report

Q	A
BC Break	no
Version	2.7

Summary

I am trying to sort by the sum of columns, which are in turn subqueries with aliases.

For example, charge_amount is the sum of the customer's total charges, and payment amount is the sum of their payments. I want to sort by their balance (charges less payments):

        $query = $this->em->createQueryBuilder()->from(Customer::class, 'customer');
        
        $query
            ->addSelect("customer.name")
            ->addSelect("({$this->getChargeAmountSubquery()}) AS charge_amount")
            ->addSelect("({$this->getPaymentAmountSubquery()}) AS payment_amount")
            ->orderBy("charge_amount-payment_amount", "ASC")
        ;

When I do this, I get the error Doctrine\\ORM\\Query\\QueryException Expected end of string, got '-'

This comment suggests wrapping the orderBy with ()

            ->orderBy("(charge_amount-payment_amount)", "ASC")

But then I get Expected Doctrine\\ORM\\Query\\Lexer::T_IDENTIFIER, got '('

If I wrap with double (( )):

            ->orderBy("((charge_amount-payment_amount))", "ASC")

I then get error Notice: Undefined index: charge_amount - it seems to lose track of the alias.

I also tried:

            ->orderBy("((charge_amount)-(payment_amount))", "ASC")

and get Argument 1 passed to Doctrine\\ORM\\Query\\AST\\ParenthesisExpression::__construct() must be an instance of Doctrine\\ORM\\Query\\AST\\Node, string given

I am using Doctrine/orm 2.7 and symfony 4.4.

Any help will be very appreciated - have been struggling with this for 2 days. Thanks for your help!

Current behavior

Errors instead of sorted results.

How to reproduce

See code samples above.

Expected behavior

Query should return results. MySQL and other databases allow for arithmetic with aliased columns in order by.

Originally created by @danielbattat on GitHub (Oct 9, 2024). ### Bug Report  | Q | A |------------ | ------ | BC Break | no | Version | 2.7 #### Summary I am trying to sort by the sum of columns, which are in turn subqueries with aliases. For example, charge_amount is the sum of the customer's total charges, and payment amount is the sum of their payments. I want to sort by their balance (charges less payments): ``` $query = $this->em->createQueryBuilder()->from(Customer::class, 'customer'); $query ->addSelect("customer.name") ->addSelect("({$this->getChargeAmountSubquery()}) AS charge_amount") ->addSelect("({$this->getPaymentAmountSubquery()}) AS payment_amount") ->orderBy("charge_amount-payment_amount", "ASC") ; ``` When I do this, I get the error `Doctrine\\ORM\\Query\\QueryException Expected end of string, got '-'` [This](https://github.com/doctrine/orm/issues/8011#issuecomment-610776675) comment suggests wrapping the orderBy with () ``` ->orderBy("(charge_amount-payment_amount)", "ASC") ``` But then I get `Expected Doctrine\\ORM\\Query\\Lexer::T_IDENTIFIER, got '('` If I wrap with double (( )): ``` ->orderBy("((charge_amount-payment_amount))", "ASC") ``` I then get error `Notice: Undefined index: charge_amount` - it seems to lose track of the alias. I also tried: ``` ->orderBy("((charge_amount)-(payment_amount))", "ASC") ``` and get `Argument 1 passed to Doctrine\\ORM\\Query\\AST\\ParenthesisExpression::__construct() must be an instance of Doctrine\\ORM\\Query\\AST\\Node, string given` I am using Doctrine/orm 2.7 and symfony 4.4. Any help will be very appreciated - have been struggling with this for 2 days. Thanks for your help! #### Current behavior Errors instead of sorted results. #### How to reproduce See code samples above. #### Expected behavior Query should return results. MySQL and other databases allow for arithmetic with aliased columns in order by.

admin referenced this issue

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

[PR #7428] CI: Test against PHP 7.3 #10482

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#7428