SqlDataProvider

API reference › yii › data › SqlDataProvider

class	`yii\data\SqlDataProvider`
Extends	`BaseDataProvider` » `yii\base\Component` » `yii\base\BaseObject`
Implements	`DataProviderInterface`, `yii\base\Configurable`
Since	2.0
Source	`framework/data/SqlDataProvider.php`

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.

Properties

Property	Type	Description	Defined by
`$behaviors` read-only	`array<yii\base\Behavior<static>>`	List of behaviors attached to this component.	`yii\base\Component`
`$count` read-only	`int`	The number of data models in the current page.	`BaseDataProvider`
`$db`	`yii\db\Connection\|array\|string`	The DB connection object or the application component ID of the DB connection. Default: `'db'`.	`SqlDataProvider`
`$id`	`string\|null`	An ID that uniquely identifies the data provider among all data providers. Default: `null`.	`BaseDataProvider`
`$key`	`string\|callable\|null`	The column that is used as the key of the data models. Default: `null`.	`SqlDataProvider`
`$keys`	`array`	The list of key values corresponding to `$models`.	`BaseDataProvider`
`$models`	`array`	The list of data models in the current page.	`BaseDataProvider`
`$pagination`	`array\|Pagination\|false`	The pagination to be used by this data provider.	`BaseDataProvider`
`$params`	`array`	Parameters (name=>value) to be bound to the SQL statement. Default: `[]`.	`SqlDataProvider`
`$sort`	`array\|Sort\|bool`	The sort definition to be used by this data provider.	`BaseDataProvider`
`$sql`	`string`	The SQL statement to be used for fetching data rows. Default: `null`.	`SqlDataProvider`
`$totalCount`	`int`	Total number of possible data models.	`BaseDataProvider`

Methods

Method	Description	Defined by
`__construct()`	Constructor.	`yii\base\BaseObject`
`__call()`	Calls the named method which is not a class method.	`yii\base\Component`
`__clone()`	This method is called after the object is created by cloning an existing one.	`yii\base\Component`
`__get()`	Returns the value of a component property.	`yii\base\Component`
`__isset()`	Checks if a property is set, i.e. defined and not null.	`yii\base\Component`
`__set()`	Sets the value of a component property.	`yii\base\Component`
`__unset()`	Sets a component property to be null.	`yii\base\Component`
`attachBehavior()`	Attaches a behavior to this component.	`yii\base\Component`
`attachBehaviors()`	Attaches a list of behaviors to the component.	`yii\base\Component`
`behaviors()`	Returns a list of behaviors that this component should behave as.	`yii\base\Component`
`canGetProperty()`	Returns a value indicating whether a property can be read.	`yii\base\Component`
`canSetProperty()`	Returns a value indicating whether a property can be set.	`yii\base\Component`
`className()` static	Returns the fully qualified name of this class.	`yii\base\BaseObject`
`detachBehavior()`	Detaches a behavior from the component.	`yii\base\Component`
`detachBehaviors()`	Detaches all behaviors from the component.	`yii\base\Component`
`ensureBehaviors()`	Makes sure that the behaviors declared in `behaviors()` are attached to this component.	`yii\base\Component`
`getBehavior()`	Returns the named behavior object.	`yii\base\Component`
`getBehaviors()`	Returns all behaviors attached to this component.	`yii\base\Component`
`getCount()`	Returns the number of data models in the current page.	`BaseDataProvider`
`getKeys()`	Returns the key values associated with the data models.	`BaseDataProvider`
`getModels()`	Returns the data models in the current page.	`BaseDataProvider`
`getPagination()`	Returns the pagination object used by this data provider.	`BaseDataProvider`
`getSort()`	Returns the sorting object used by this data provider.	`BaseDataProvider`
`getTotalCount()`	Returns the total number of data models.	`BaseDataProvider`
`hasEventHandlers()`	Returns a value indicating whether there is any handler attached to the named event.	`yii\base\Component`
`hasMethod()`	Returns a value indicating whether a method is defined.	`yii\base\Component`
`hasProperty()`	Returns a value indicating whether a property is defined for this component.	`yii\base\Component`
`init()`	Initializes the DB connection component.	`SqlDataProvider`
`off()`	Detaches an existing event handler from this component.	`yii\base\Component`
`on()`	Attaches an event handler to an event.	`yii\base\Component`
`prepare()`	Prepares the data models and keys.	`BaseDataProvider`
`refresh()`	Refreshes the data provider.	`BaseDataProvider`
`setKeys()`	Sets the key values associated with the data models.	`BaseDataProvider`
`setModels()`	Sets the data models in the current page.	`BaseDataProvider`
`setPagination()`	Sets the pagination for this data provider.	`BaseDataProvider`
`setSort()`	Sets the sort definition for this data provider.	`BaseDataProvider`
`setTotalCount()`	Sets the total number of data models.	`BaseDataProvider`
`trigger()`	Triggers an event.	`yii\base\Component`
`prepareKeys()` protected	Prepares the keys associated with the currently available data models.	`SqlDataProvider`
`prepareModels()` protected	Prepares the data models that will be made available in the current page.	`SqlDataProvider`
`prepareTotalCount()` protected	Returns a value indicating the total number of data models in this data provider.	`SqlDataProvider`

Property details

$db

public yii\db\Connection|array|string $db = 'db'

The DB connection object or the application component ID of the DB connection.

Starting from version 2.0.2, this can also be a configuration array for creating the object.

$key

public string|callable|null $key = null

The column that is used as the key of the data models.

This can be either a column name, or a callable that returns the key value of a given data model.

If this is not set, the keys of the $models array will be used.

Method details

init()

public function init(): mixed

Initializes the DB connection component.

This method will initialize the $db property to make sure it refers to a valid DB connection.

Returns

mixed

Throws

yii\base\InvalidConfigException: if $db is invalid.

prepareKeys()

protected function prepareKeys(mixed $models): array

Prepares the keys associated with the currently available data models.

Parameters

Parameter	Type	Description
`$models`	`mixed`	The available data models

Returns

array: The keys

prepareModels()

protected function prepareModels(): array

Prepares the data models that will be made available in the current page.

Returns

array: The available data models

prepareTotalCount()

protected function prepareTotalCount(): int

Returns a value indicating the total number of data models in this data provider.

Returns

int: Total number of data models in this data provider.