Arithmetic Operations in OrderBy with Aliases

[danielbattat]

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.