CODEWITHSUNDEEP
phpyii2
Федор Досумов
Федор Досумов

Reputation: 3

How to invoke function only once in GridView?

I want to warn: I can't use array, only Active Record.

So I have a GridView and columns:

$gridColumns = [
    ['class' => SerialColumn::class],
    [
        'attribute' => 'cap',
        'label' => 'Всего, Зан., Св.',
        'format' => 'raw',
        'value' => function($model) {
            $data = Yii::$app->db->createCommand("SELECT * FROM dbFunction({$model->point_id}")->queryOne();
            $html = <<< HTML
<div class="item">{$data['capacity']}</div>
<div class="item">{$data['occupied']}</div>
<div class="item">{$data['free']}</div>
HTML;       
            return $html;
        },      
    ],  
];

echo GridView::widget([
    'id' => 'my-table',
    'columns' => $gridColumns,
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,  
    'rowOptions' => function($model) {
        $data = Yii::$app->db->createCommand("SELECT * FROM dbFunction({$model->point_id}")->queryOne();        
        
        $class = $data['free'] === 0 ? 'free-point' : '';
                
        if ($data['capacity'] === 0 && $data['occupied'] === 0 && $data['free'] === 0)
            $class = 'd-none';
        
        return [
            'data-id' => $model->id,
            'class' =>  $class,
        ];
    },
]);

It works correctly, but dbFunction is executing two times for each row (first time for a cell and the second - for row options). This function is a very heavyweight.

The question is: is there a way to execute this function only once (for each iteration) and use the result of the function in both places? Don't know how to do it, because under the hood Yii2 uses loop for columns and, I guess, separate invoke for rowOptions. How can I combine it?

P.S. Of course I can write conditions for rowOptions using js, but then will be a bug: it will be written "shows 30 of 60 records", but in facts it will be shown only 5 (or else). That's why I want to solve the problem on the backend.

Upvotes: 0

Views: 110

Answers (2)

Daniel G
Daniel G

Reputation: 174

I think you can use try using query cache for this purpose. Something like this:

Yii::$app->db->cache(function ($db, $model) {
        return $db->createCommand(
            "SELECT * FROM dbFunction({$model->point_id})"
        )->queryOne();
});

This way the second call will return cached results.

P.S. You would also need to configure Cache component and query cache in db config.

Upvotes: 0

Michal Hynčica
Michal Hynčica

Reputation: 6179

I would suggest moving the function that loads data into model and store the result in private property of model.

For example something like this:

class MyModel extends ActiveRecord
{
    private ?array $capacityData = null;
    
    public function capacityData(): array
    {
        if ($this->capacityData === null) {
            $this->capacityData  = $this->getDb()->createCommand(
                "SELECT * FROM dbFunction({$model->point_id})"
            )->queryOne();
        }

        return $this->capacityData;
    }

    // ... other definitions ...
}

Then you can use it in multiple places like this:

'value' => function($model) {
     $data = $model->capacityData();
     $html = <<< HTML
<div class="item">{$data['capacity']}</div>
<div class="item">{$data['occupied']}</div>
<div class="item">{$data['free']}</div>
HTML;       
     return $html;
},

Or like this:

'rowOptions' => function($model) {
    $data = $model->capacityData();        
        
    $class = $data['free'] === 0 ? 'free-point' : '';
                
    if ($data['capacity'] === 0 && $data['occupied'] === 0 && $data['free'] === 0)
        $class = 'd-none';
        
    return [
        'data-id' => $model->id,
        'class' =>  $class,
    ];
},

The SQL query will be executed only during first call of capacityData() function. During next call the data will be already available in $capacityData property.

Btw. adding class in $rowOptions property that hides the row won't solve the problem with wrong number in text "shows 30 of 60 records". The only way that text will show correct numbers will be if the data provider doesn't contain rows that shouldn't be displayed.

Upvotes: 1

Related Questions