Using query builder instead of ActiveQuery always return $key from 0 in ActionColumn class datagrid [solved]

Yii 2.0
1

Hi, I have customized query in my index file. When it is showed into datagrid, the actionColumn (yii or kartik) did not match with the $model->id.
It started with index 0, while the $model->id started with 1.
how i customized the actionColumn with $model->id ?

this is my model search file

public function search($params)
    {
        //$query = ItemSparepart::find();
        $query = (new Query())->select(['its.id', 'its.name', 'its.remark', 'its.listed',
                'sum(stock.qty) as qty', 'its.satuan_id', 'satuan.name as satuan_name'])
                ->from('item_sparepart its')
                ->leftJoin('stock', 'stock.item_id = its.id')
                ->leftJoin('satuan', 'satuan.id = its.satuan_id')
                ->groupBy('stock.item_id');

        // add conditions that should always apply here

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

        $this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        // grid filtering conditions
        $query->andFilterWhere([
            'id' => $this->id,
            'listed' => $this->listed,
            'qty' => $this->qty,
            //'created_by' => $this->created_by,
            'created_time' => $this->created_time,
            //'updated_by' => $this->updated_by,
            'updated_time' => $this->updated_time,
        ]);

        $query->andFilterWhere(['like', 'name', $this->name])
            ->andFilterWhere(['like', 'remark', $this->remark])
            ->andFilterWhere(['like', 'satuan.name', $this->satuan_id])
            ->andFilterWhere(['like', 'category_id', $this->category_id]);

        return $dataProvider;
    }

and this is my index file

<?php 
        $gridColumns = [
            
            [
                'class' => 'kartik\grid\ActionColumn',
                'dropdown' => false,
                'vAlign'=>'middle',
                
            ],     
            'id',
            'name',
            ['attribute' => 'qty', 'format' => ['decimal', 2], 'hAlign' => 'right'],
            ['attribute' => 'satuan_id', 'value' => 'satuan_name'],
            //'satuan_name',
            ['attribute' => 'category_id', 'value' => 'category.name'],
            [
                'class' => 'kartik\grid\BooleanColumn',
                'attribute' => 'listed'
            ],
            'remark:ntext',
        ];
    ?>
    <?= GridView::widget([
        'id' => 'kv-grid-item-sparepart',
        'dataProvider'=>$dataProvider,
        'filterModel'=>$searchModel,
        'columns'=>$gridColumns,
        'resizableColumns'=>true,
        'containerOptions'=>['style'=>'overflow: auto'], // only set when $responsive = false
        'headerRowOptions'=>['class'=>'kartik-sheet-style'],
        'filterRowOptions'=>['class'=>'kartik-sheet-style'],
        'pjax'=>true, // pjax is set to always true for this demo
        'toolbar' => [
            [
                'content' =>
                    Html::button('<i class="fa fa-plus"></i>', [
                        'value' => Yii::$app->urlManager->createUrl('/item-sparepart/create'),
                        'class' => 'btn btn-success showModal',
                        'id' => 'BtnCreateItemSparepart',
                        'title' =>  Yii::t('kvgrid', 'Add Item Sparepart'),
                    ]) . ' ' .
                    Html::a('<i class="fa fa-refresh"></i>', ['/item-sparepart'], [
                        'class' => 'btn btn-default',
                        'title'=>Yii::t('kvgrid', 'Reset Grid'),
                        'data-pjax' => 0,
                    ]),
                'options' => ['class' => 'btn-group mr-2']
            ],
            '{export}',
            '{toggleData}'
        ],
        'export'=>[
            'fontAwesome'=>true
        ],
        // parameters from the demo form
        'bordered'=>true,
        'striped'=>false,
        'condensed'=>true,
        'responsive'=>false,
        'hover'=>true,
        'showPageSummary'=>false,
        'panel'=>[
            'type'=>GridView::TYPE_PRIMARY,            
        ],
        'persistResize'=>false,
        'exportConfig'=>$defaultExportConfig,
    ]); ?>

image
image1309×868 174 KB

2

found same problem here.
php - ActionColumn Yii2, $key always return 0 based from searchModel - Stack Overflow

3

Hi,
Check the generated keys of the dataprovider when it is created, e.g. with VarDumper::dump, in your search method.
If I understand your code correctly you are using a Query object for creating the dataProvider query, not an ActiveQuery, though you use an activeDataProvider.

According to api for ActiveDataProvider https://www.yiiframework.com/doc/api/2.0/yii-data-activedataprovider#$key-detail:

the following rules will be used to determine the keys of the data models:

I suspect the second case applies in your case, so the keys are the array indexes of the model array, zero-based.

rgds Gunnar

4

this I tried to VarDump::dump($query). here is the result .

 yii\db\Query#1
(
    [yii\base\Component:_events] => []
    [yii\base\Component:_eventWildcards] => []
    [yii\base\Component:_behaviors] => null
    [select] => [
        'its.id' => 'its.id'
        'key' => 'its.id'
        'its.name' => 'its.name'
        'its.remark' => 'its.remark'
        'its.listed' => 'its.listed'
        'qty' => 'sum(stock.qty)'
        'its.satuan_id' => 'its.satuan_id'
        'satuan_name' => 'satuan.name'
        'its.category_id' => 'its.category_id'
        'category_name' => 'category.name'
    ]
    [selectOption] => null
    [distinct] => false
    [from] => [
        0 => 'item_sparepart its'
    ]
    [groupBy] => [
        0 => 'stock.item_id'
    ]
    [join] => [
        0 => [
            0 => 'LEFT JOIN'
            1 => 'stock'
            2 => 'stock.item_id = its.id'
        ]
        1 => [
            0 => 'LEFT JOIN'
            1 => 'satuan'
            2 => 'satuan.id = its.satuan_id'
        ]
        2 => [
            0 => 'LEFT JOIN'
            1 => 'category'
            2 => 'category.id = its.category_id'
        ]
    ]
    [having] => null
    [union] => null
    [withQueries] => null
    [params] => []
    [queryCacheDuration] => null
    [queryCacheDependency] => null
    [where] => null
    [limit] => null
    [offset] => null
    [orderBy] => null
    [indexBy] => null
    [emulateExecution] => false
)

I saw the key was already the model’s id.

5

Ok, but what is the result if you dump the arrays from the dataProvider?
VarDumper::dump($dataProvider->getModels(), before the return of the dataProvider.
I assume the array keys is a zero-based count of models.

But, you can set the key to use by explicitly setting the property “key” of the dataProvider, to a column in the model, id in your case.
See the link in my previous post.
rgds Gunnar

6

here is the result dump for $dataProvider before return it.

[
    0 => [
        'id' => 1
        'key' => 1
        'name' => 'BREAKER 250 A EZC250F3250MG'
        'remark' => ''
        'listed' => 1
        'qty' => '2.00'
        'satuan_id' => 1
        'satuan_name' => 'Pcs'
        'category_id' => 3
        'category_name' => 'PKS Used'
    ]
    1 => [
        'id' => 2
        'key' => 2
        'name' => 'PLAT 25 MM X 4\' X 8\''
        'remark' => 'BERAT 1 LEMBAR PLAT :
RUMUS = 23,3 X TEBAL PLAT UKURAN 4X8
              = 23,3 X 25 = 582,5 KG'
        'listed' => 1
        'qty' => '10.00'
        'satuan_id' => 6
        'satuan_name' => 'Lembar'
        'category_id' => 3
        'category_name' => 'PKS Used'
    ]
    2 => [
        'id' => 3
        'key' => 3
        'name' => 'VBelt B36'
        'remark' => 'utk mesin pompa air/conveyor
U/KOMPRESOR PANJANG PIBER'
        'listed' => 1
        'qty' => '28.00'
        'satuan_id' => 1
        'satuan_name' => 'Pcs'
        'category_id' => 3
        'category_name' => 'PKS Used'
    ]
    3 => [
        'id' => 4
        'key' => 4
        'name' => 'Mata Bor Nichi 18mm'
        'remark' => ''
        'listed' => 1
        'qty' => '8.00'
        'satuan_id' => 1
        'satuan_name' => 'Pcs'
        'category_id' => 3
        'category_name' => 'PKS Used'
    ]
    4 => [
        'id' => 5
        'key' => 5
        'name' => 'BEARING 6305 2 RSI C3'
        'remark' => 'BEARING POMPA BOILER FEEDING PUMP MERK KSB
ZZ TUTUP KALENG
2RS1 TUTUP KARET'
        'listed' => 1
        'qty' => '1.00'
        'satuan_id' => 1
        'satuan_name' => 'Pcs'
        'category_id' => 3
        'category_name' => 'PKS Used'
    ]
    5 => [
        'id' => 6
        'key' => 6
        'name' => 'Vbelt B45'
        'remark' => ''
        'listed' => 1
        'qty' => '45.00'
        'satuan_id' => 1
        'satuan_name' => 'Pcs'
        'category_id' => 3
        'category_name' => 'PKS Used'
    ]
]

how to set the key explicitly?

7

Hi,
see my previous answer:
" But, you can set the key to use by explicitly setting the property “key” of the dataProvider , to a column in the model, id in your case.
See the link in my previous post."

regards Gunnar

8

thanks for your clue.
I already solved it by defining the key inside active data provider

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

:clap:
regards G.