Yii2 Gridview Sort By Caculated Field

I have customer table and need to show the customer's balance that need to calculate from another table.

Now on Customer Model I have added Getter function

public function getBalance(){
        $customer_id = $this->id;

        $connection = Yii::$app->getDb();


        $sql = "SELECT * FROM customer_transaction WHERE customer_id = ". $customer_id ." ORDER BY transaction_date ASC , id ASC";
    
        $command = $connection->createCommand($sql);
        $results = $command->queryAll();

        $balance = 0;
        foreach($results as $result){
            $balance = $result['balance'];
        }

        return $balance;
    }

On CustomerSearch Model I also add

public $balance
public function rules()
    {
        return [
        
            [['balance'], 'safe'],
        ];
    }

On CustomerSearch Model search function I also added this code. The balance value show correct value but balance field is not sorted DESC

$dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);


        $dataProvider->setSort([
            'attributes' => [
                
                'balance' => [
                    'default' => SORT_DESC
                ],
                
            ]
        ]);


        $this->load($params);

Could you please tell what's wrong with my code?

According to yii2 practice, firstly you need to create 'CustomerTransaction' model (if not have). Then in search model add subquery and change your DataProvider initialization block to

    $subQuery = CustomerTransaction::find()->select('customer_id, sum(balance) as balance')->groupBy('customer_id');
    $query->leftJoin(['balance' => $subQuery], 'balance.customer_id = id');
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort' => [
            'attributes' => [
                'name', // all your attributes to sorting
                'balance',
            ],
            'defaultOrder' => [
                'balance' => SORT_DESC,
            ],
        ],
    ]);

I strongly recommend you to refactor the getBalance() method: minimum change '=' to '+=' in the 'foreach' cycle, but better change query to 'SELECT SUM(balance)...'.

This article can help you.