Yii2 Gridview Sort By Caculated Field

Question

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?

Answer 1

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.