Yii2 GridView SqlDataprovider sort not working

user3021615

I have the following query:

$query = (new \yii\db\Query())
            ->select([
                'p.id',
                'p.name',
                'c.name AS contact_name',
                'l.name AS laboratory',
                'p.status',
                'p.address',
                'p.start_date',
                'p.city',
                'p.updated_at',
                'stortbon.attribute_value as Stortbon',
                'vrijgave.attribute_value as Vrijgave'
            ])
            ->from(['project p'])
            ->innerJoin('contact c', 'p.contact_id = c.id')
            ->innerJoin('contact l', 'p.contact_id = l.id')
            ->innerJoin(['stortbon' => $stortbon_query], 'p.id = stortbon.model_id')
            ->innerJoin(['vrijgave' => $vrijgave_query], 'p.id = vrijgave.model_id');

And i am using a SqlDataprovider to get my results in a Gridview:

$dataProvider = new SqlDataProvider([
        'sql' => $query->createCommand()->sql,
        'totalCount' => $query->count(),
        'key'        => 'id',
        'sort' => [
            'attributes' => [
                'start_date' => [
                    'asc' => ['start_date' => SORT_ASC],
                    'desc' => ['start_date' => SORT_DESC],
                    'default' => SORT_DESC,
                    'label' => 'Start Datum',
                ],
            ],
        ],
    ]);

gridview and columns:

            $columns[] = [
                    'header' => Yii::t('project', 'Start date'),
                    'attribute' => 'start_date',
                    'format' => 'date',
                    'filter' => false,
                ];

GridView::widget([
            'dataProvider' => $dataProvider,
            'filterModel'  => $searchModel,
            'columns'      => $columns,
        ]);

I've also tried to change the name to p.start_date but it's showing the gridview but not the sort button/title link to sort the data descending or ascending

vasillis

In Gridview widget remove the filter model because you can't combine a sqldataprovider with an searchmodel

GridView::widget([
    'dataProvider' => $dataProvider,
    'columns'      => $columns,
]);

and the to make available shorting in sqldataprovider in grid columns you have to declare in short array

$dataProvider = new SqlDataProvider([
    'sql' => $query->createCommand()->sql,
    'totalCount' => $query->count(),
    'key'        => 'id',
    'sort' => [
        'defaultOrder' => ['start_date'=>SORT_DESC],
        'attributes' => [
            'start_date',
            'status',
            'address',
            ],
        ],
    ],
]);

Collected from the Internet

Please contact [email protected] to delete if infringement.