ActiveDataProvider and defaultOrder

Hello all,

I’m trying to populate a GridView using an ActiveDataProvider as you can see below:

$query = new \yii\db\Query();

        

        $query->select(['gruppo.ID as gruppoID', 'users.ID as usersID', 'users.nickname', 'users.nome', 'users.cognome'])

              ->from('gruppo')

              ->innerJoin('gruppo_has_users', 'gruppo.ID = gruppo_has_users.gruppo_ID')

              ->innerJoin('users', 'gruppo_has_users.users_ID = users.ID')

              ->where('gruppo.ID = :gruppoID', ['gruppoID' => $this->ID]);             

        

        $dataProvider = new ActiveDataProvider([

            'query' => $query,

            'sort' => [

                'defaultOrder' => [

                    'users.cognome' => SORT_ASC,

                    'users.nome' => SORT_DESC

                    ]

                ],            

        ]);

Here the call to the view:

return $this->render('modifica', [

                'model' => $modelForm,

                'dataProviderIscritti' => $model->getIscrittiAsDataProvider()

            ]);

And the view:

<div class="table-scroller">

                                        <?= GridView::widget([

                                            'dataProvider' => $dataProviderIscritti,

                                            'columns' => [

                                                ['class' => 'yii\grid\SerialColumn'],

                                                [

                                                    'class' => 'yii\grid\DataColumn', // can be omitted, default

                                                    'attribute' => 'cognome',                        

                                                    'label' => 'cognome',

                                                    'enableSorting' => true,

                                                    'headerOptions' => ['style' => 'min-width:30px;'],

                                                ],                                       

                                                [

                                                    'class' => 'yii\grid\DataColumn', // can be omitted, default

                                                    'attribute' => 'nome',                                                    

                                                    'label' => 'Nome',

                                                    'enableSorting' => true,

                                                    'headerOptions' => ['style' => 'min-width:50px;'],

                                                ],

                                                [

                                                    'class' => 'yii\grid\DataColumn', // can be omitted, default

                                                    'attribute' => 'nickname',

                                                    'label' => 'Nickname',

                                                    'enableSorting' => true,

                                                    'headerOptions' => ['style' => 'min-width:50px;'],

                                                ],  

                                                [

                                                    'class' => 'yii\grid\ActionColumn', 

                                                    'template' => '{delete}',

                                                    'contentOptions' => ['style' => 'min-width:50px;', 'class' => 'text-center'],


                                                    'buttons' => 

                                                        [  

                                                            'delete' => function ($url, $model, $key) {                        

                                                                return '<a href="'.Url::to(['gruppi/eliminautente', ['gruppoID' => $model['gruppoID'], 'usersID' => $model['usersID']]]).'"><span class="glyphicon glyphicon-pencil"></span></a>';

                                                            },

                                                        ]

                                                ]

                                            ],

                                        ]); ?>

                                    </div>

If I remove from the ActiveDataProvider definition the defaultOrder clause all works fine, otherwise I got the error:

Invalid argument supplied for foreach()

What’s wrong in your opinion?

Thank you!!

I think you need to eargly load the relation (‘users’) becuase the order dosent know what user.nome is at the time of ordering. The default way to do this in yii is to use joinWith(‘yourUserRelationName’). Also since you are doing a direct query and not using a model you can use SqlDataProvider instead of ActiveDataProvider.

Thank you!! you guided me to the solution…

Changing the sort section as you see below, it worked fine

'sort' => [

                'attributes' => [

                    'cognome',

                    'nome'

                    ],

                'defaultOrder' => [

                    'cognome' => SORT_ASC,

                    'nome' => SORT_ASC                    

                    ]

                ],      

thank you!!