Yii Framework 2 : SqlDataProvider

Yii2.0 SqlDataProvider is used to get the data provider using plain SQL statement. SqlDataProvider is return the data provider as array for each row of query result. We can use the sorting, pagination in SqlDataProvider using $sort and $pagination parameter.

Pagination: If we want pagination concept, We have to configure the $totalCount attribute.

Sort : If we want to use the sorting feature, We have to configure the $sort attribute. If you want to remove the sorting, set ‘sort’ as false.

SqlDataProvider Formats

FORMAT 1

<?php
$totalCount = Yii::$app->db
            ->createCommand('SELECT COUNT(*) FROM posts WHERE publish=:publish', [':publish' => 1])
            ->queryScalar();

$dataProvider = new SqlDataProvider([
    'sql' => 'SELECT * FROM posts WHERE publish=:publish',
    'params' => [':publish' => 1],
    'totalCount' => $totalCount,
    //'sort' =>false, to remove the table header sorting
    'sort' => [
        'attributes' => [
            'title' => [
                'asc' => ['title' => SORT_ASC],
                'desc' => ['title' => SORT_DESC],
                'default' => SORT_DESC,
                'label' => 'Post Title',
            ],
            'author' => [
                'asc' => ['author' => SORT_ASC],
                'desc' => ['author' => SORT_DESC],
                'default' => SORT_DESC,
                'label' => 'Name',
            ],
			'created_on'
        ],
    ],
    'pagination' => [
        'pageSize' => 10,
    ],
]);

return $dataProvider;
?>

SqlDataProvider To Gridview

We will get the array for each row. Use the key to display/access the value in gridview of yii2.0.

<?php
<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        [
            'label' =>"Name",
            'attribute' => 'tbl_column_name',
            'value'=>function($data){
                return $data["tbl_column_name"];
            }
        ],
        'title',
        'author',
        'created_on',
        ['class' => 'yii\grid\ActionColumn'],
    ],
]);
?>

SqlDataProvider To Excel

GridView/SqlDataProvider To Excel