# SqlDataProvider

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

[API reference](/docs/api/index.md) › [yii](/docs/api/yii/index.md) › [data](/docs/api/yii/data/index.md) › SqlDataProvider

| class | `yii\data\SqlDataProvider` |
| --- | --- |
| Extends | [`BaseDataProvider`](/docs/api/yii/data/basedataprovider/index.md) » [`yii\base\Component`](/docs/api/yii/base/component/index.md) » [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| Implements | [`DataProviderInterface`](/docs/api/yii/data/dataproviderinterface/index.md), [`yii\base\Configurable`](/docs/api/yii/base/configurable/index.md) |
| Since | 2.0 |
| Source | [`framework/data/SqlDataProvider.php`](https://github.com/yiisoft/yii2/blob/22.0/framework/data/SqlDataProvider.php#L67) |

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`](/docs/api/yii/data/sqldataprovider/index.md#property-sql) statement with "ORDER BY" and "LIMIT"
clauses. You may configure the [`$sort`](/docs/api/yii/data/basedataprovider/index.md#property-sort) and [`$pagination`](/docs/api/yii/data/basedataprovider/index.md#property-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`](/docs/api/yii/data/basedataprovider/index.md#property-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`](/docs/api/yii/data/basedataprovider/index.md#property-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](/docs/guide/output-data-providers/index.md).

## Properties

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

## Methods

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

## Property details

### $db <span id="property-db"></span>

```php
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 <span id="property-key"></span>

```php
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`](/docs/api/yii/data/basedataprovider/index.md#property-models) array will be used.

## Method details

### init() <span id="method-init"></span>

```php
public function init(): mixed
```

Initializes the DB connection component.

This method will initialize the [`$db`](/docs/api/yii/data/sqldataprovider/index.md#property-db) property to make sure it refers to a valid DB connection.

**Returns**

`mixed`

**Throws**

- [`yii\base\InvalidConfigException`](/docs/api/yii/base/invalidconfigexception/index.md): if [`$db`](/docs/api/yii/data/sqldataprovider/index.md#property-db) is invalid.

### prepareKeys() <span id="method-prepareKeys"></span>

```php
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() <span id="method-prepareModels"></span>

```php
protected function prepareModels(): array
```

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

**Returns**

`array`: The available data models

### prepareTotalCount() <span id="method-prepareTotalCount"></span>

```php
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.