Class yii\data\SqlDataProvider

SqlDataProvider implements a data provider based on a plain SQL statement.

SqlDataProvider provides data in terms of arrays, each representing a row of query result.

Like other data providers, SqlDataProvider also supports sorting and pagination. It does so by modifying the given $sql statement with "ORDER BY" and "LIMIT" clauses. You may configure the $sort and $pagination properties to customize sorting and pagination behaviors.

SqlDataProvider may be used in the following way:

$count = Yii::$app->db->createCommand('
    SELECT COUNT(*) FROM user WHERE status=:status
', [':status' => 1])->queryScalar();

$dataProvider = new SqlDataProvider([
    'sql' => 'SELECT * FROM user WHERE status=:status',
    'params' => [':status' => 1],
    'totalCount' => $count,
    'sort' => [
        'attributes' => [
            'age',
            'name' => [
                'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
                'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
                'default' => SORT_DESC,
                'label' => 'Name',
            ],
        ],
    ],
    'pagination' => [
        'pageSize' => 20,
    ],
]);

// get the user records in the current page
$models = $dataProvider->getModels();

Note: if you want to use the pagination feature, you must configure the $totalCount property to be the total number of rows (without pagination). And if you want to use the sorting feature, you must configure the $sort property so that the provider knows which columns can be sorted.

For more details and usage information on SqlDataProvider, see the guide article on data providers.