CODEWITHSUNDEEP
phpgridviewyii2
MiglyCH
MiglyCH

Reputation: 49

yii2 Ignore pagination for calculation

I have a GridView table where I want to calculate the balance, because in the DB there are just debits and credits. I added in the Model a new object-variable balance and a class-var tempBalance which holds the last balance.

In the SearchModel I have the following lines to calculate the balance

private function calculateBalance($dataProvider) {
    foreach ($dataProvider->models as $model) {
        $model->balance = parent::$tempBalance + $model->debit - $model->credit;
            parent::$tempBalance = $model->balance;
        }
}

This works fine, but when I have more than 20 results in the GridView it generates automatically pages with each 20 records (I want to keep this pagination). The Problem is that on every page the saldo starts with 0, but I want to have still the saldo of the last entry of the previous page apparently.

I've tried to turn off the paginatinon for the calculation, and then turn it on again, but thats where I struggled.

I tried to turn off pagination before the foreach-loop in the calculateBalance() method:

$pagination = $dataProvider->getPagination();
$dataProvider->setPagination(false);

and than turn it on again after the loop by the following code:

$dataProvider->setPagination($pagination);

But this didn't turned on the pagination again. The pagination was still turned off.

Is there a way to ignore the pagination for my calculation or is it the right way to turn the pagination off and on again?

How could I turn on the pagination after turning it off?

Upvotes: 0

Views: 386

Answers (1)

rob006
rob006

Reputation: 22174

$dataProvider->models is cached, if you're changing pagination settings, you must call $dataProvider->prepare(true) to force data provider to refetch data from database.

Something like:

// setup $dataProvider
// render GridView

$dataProvider->setPagination(false);
$dataProvider->prepare(true);
$this->calculateBalance($dataProvider);

But calculating balance on models is a really bad idea - if someone will have millions of records, it will be really slow and finally you will get out of memory errors. You should calculate balance via SQL query - it will be faster and will not blow up for big sets of data.

$balanceQuery = clone $dataProvider->query;
$balanceQuery->select([
    'debit' => new Expression('SUM(debit)'),
    'credit' => new Expression('SUM(credit)'),
]);
$balanceData = $balanceQuery->asArray()->one();
$balance = $initialBalance + $balanceData['debit'] - $balanceData['credit'];

Note that filtering of grid data will change balance (since $dataProvider->query will ignore some data). If this is not intentional, you should probably clone $query before applying filtering or use fresh query, like $balanceQuery = MyModel::find()->onlyForUser($userId);.

Upvotes: 1

Related Questions