API Documentation for Yii 1.1
0 follower
CSqlDataProvider
| Package | system.web |
|---|---|
| Inheritance | class CSqlDataProvider » CDataProvider » CComponent |
| Implements | IDataProvider |
| Since | 1.1.4 |
| Source Code | framework/web/CSqlDataProvider.php |
CSqlDataProvider implements a data provider based on a plain SQL statement.
CSqlDataProvider provides data in terms of arrays, each representing a row of query result.
Like other data providers, CSqlDataProvider 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.
CSqlDataProvider may be used in the following way:
Note: if you want to use the pagination feature, you must configure the totalItemCount property to be the total number of rows (without pagination). And if you want to use the sorting feature, you must configure sort property so that the provider knows which columns can be sorted.
CSqlDataProvider provides data in terms of arrays, each representing a row of query result.
Like other data providers, CSqlDataProvider 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.
CSqlDataProvider may be used in the following way:
$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM tbl_user')->queryScalar();
$sql='SELECT * FROM tbl_user';
$dataProvider=new CSqlDataProvider($sql, array(
'totalItemCount'=>$count,
'sort'=>array(
'attributes'=>array(
'id', 'username', 'email',
),
),
'pagination'=>array(
'pageSize'=>10,
),
));
// $dataProvider->getData() will return a list of arrays.
Note: if you want to use the pagination feature, you must configure the totalItemCount property to be the total number of rows (without pagination). And if you want to use the sorting feature, you must configure sort property so that the provider knows which columns can be sorted.
Public Properties
| Property | Type | Description | Defined By |
|---|---|---|---|
| data | array | Returns the data items currently available. | CDataProvider |
| db | CDbConnection | the database connection to be used in the queries. | CSqlDataProvider |
| id | string | Returns the ID that uniquely identifies the data provider. | CDataProvider |
| itemCount | integer | Returns the number of data items in the current page. | CDataProvider |
| keyField | string | the name of key field. | CSqlDataProvider |
| keys | array | Returns the key values associated with the data items. | CDataProvider |
| pagination | CPagination|false | Returns the pagination object. | CDataProvider |
| params | array | parameters (name=>value) to be bound to the SQL statement. | CSqlDataProvider |
| sort | CSort|false | Returns the sort object. | CDataProvider |
| sql | string|CDbCommand | the SQL statement to be used for fetching data rows. | CSqlDataProvider |
| totalItemCount | integer | Returns the total number of data items. | CDataProvider |
Public Methods
| Method | Description | Defined By |
|---|---|---|
| __call() | Calls the named method which is not a class method. | CComponent |
| __construct() | Constructor. | CSqlDataProvider |
| __get() | Returns a property value, an event handler list or a behavior based on its name. | CComponent |
| __isset() | Checks if a property value is null. | CComponent |
| __set() | Sets value of a component property. | CComponent |
| __unset() | Sets a component property to be null. | CComponent |
| asa() | Returns the named behavior object. | CComponent |
| attachBehavior() | Attaches a behavior to this component. | CComponent |
| attachBehaviors() | Attaches a list of behaviors to the component. | CComponent |
| attachEventHandler() | Attaches an event handler to an event. | CComponent |
| canGetProperty() | Determines whether a property can be read. | CComponent |
| canSetProperty() | Determines whether a property can be set. | CComponent |
| detachBehavior() | Detaches a behavior from the component. | CComponent |
| detachBehaviors() | Detaches all behaviors from the component. | CComponent |
| detachEventHandler() | Detaches an existing event handler. | CComponent |
| disableBehavior() | Disables an attached behavior. | CComponent |
| disableBehaviors() | Disables all behaviors attached to this component. | CComponent |
| enableBehavior() | Enables an attached behavior. | CComponent |
| enableBehaviors() | Enables all behaviors attached to this component. | CComponent |
| evaluateExpression() | Evaluates a PHP expression or callback under the context of this component. | CComponent |
| getData() | Returns the data items currently available. | CDataProvider |
| getEventHandlers() | Returns the list of attached event handlers for an event. | CComponent |
| getId() | Returns the ID that uniquely identifies the data provider. | CDataProvider |
| getItemCount() | Returns the number of data items in the current page. | CDataProvider |
| getKeys() | Returns the key values associated with the data items. | CDataProvider |
| getPagination() | Returns the pagination object. | CDataProvider |
| getSort() | Returns the sort object. | CDataProvider |
| getTotalItemCount() | Returns the total number of data items. | CDataProvider |
| hasEvent() | Determines whether an event is defined. | CComponent |
| hasEventHandler() | Checks whether the named event has attached handlers. | CComponent |
| hasProperty() | Determines whether a property is defined. | CComponent |
| raiseEvent() | Raises an event. | CComponent |
| setData() | Sets the data items for this provider. | CDataProvider |
| setId() | Sets the provider ID. | CDataProvider |
| setKeys() | Sets the data item keys for this provider. | CDataProvider |
| setPagination() | Sets the pagination for this data provider. | CDataProvider |
| setSort() | Sets the sorting for this data provider. | CDataProvider |
| setTotalItemCount() | Sets the total number of data items. | CDataProvider |
Protected Methods
| Method | Description | Defined By |
|---|---|---|
| calculateTotalItemCount() | Calculates the total number of data items. | CSqlDataProvider |
| fetchData() | Fetches the data from the persistent data storage. | CSqlDataProvider |
| fetchKeys() | Fetches the data item keys from the persistent data storage. | CSqlDataProvider |
Property Details
db
property
public CDbConnection $db;
the database connection to be used in the queries. Defaults to null, meaning using Yii::app()->db.
keyField
property
public string $keyField;
the name of key field. Defaults to 'id'.
params
property
public array $params;
parameters (name=>value) to be bound to the SQL statement.
sql
property
public string|CDbCommand $sql;
the SQL statement to be used for fetching data rows. Since version 1.1.13 this can also be an instance of CDbCommand.
Method Details
__construct()
method
|
public void __construct(string|CDbCommand $sql, array $config=array (
))
| ||
| $sql | string|CDbCommand | the SQL statement to be used for fetching data rows. Since version 1.1.13 this can also be an instance of CDbCommand. |
| $config | array | configuration (name=>value) to be applied as the initial property values of this class. |
Source Code: framework/web/CSqlDataProvider.php#64 (show)
public function __construct($sql,$config=array())
{
$this->sql=$sql;
foreach($config as $key=>$value)
$this->$key=$value;
}
Constructor.
calculateTotalItemCount()
method
|
protected integer calculateTotalItemCount()
| ||
| {return} | integer | the total number of data items. |
Source Code: framework/web/CSqlDataProvider.php#138 (show)
protected function calculateTotalItemCount()
{
return 0;
}
Calculates the total number of data items. This method is invoked when getTotalItemCount() is invoked and totalItemCount is not set previously. The default implementation simply returns 0. You may override this method to return accurate total number of data items.
fetchData()
method
|
protected array fetchData()
| ||
| {return} | array | list of data items |
Source Code: framework/web/CSqlDataProvider.php#75 (show)
protected function fetchData()
{
if(!($this->sql instanceof CDbCommand))
{
$db=$this->db===null ? Yii::app()->db : $this->db;
$command=$db->createCommand($this->sql);
}
else
$command=clone $this->sql;
if(($sort=$this->getSort())!==false)
{
$order=$sort->getOrderBy();
if(!empty($order))
{
if(preg_match('/\s+order\s+by\s+[\w\s,\.]+$/i',$command->text))
$command->text.=', '.$order;
else
$command->text.=' ORDER BY '.$order;
}
}
if(($pagination=$this->getPagination())!==false)
{
$pagination->setItemCount($this->getTotalItemCount());
$limit=$pagination->getLimit();
$offset=$pagination->getOffset();
$command->text=$command->getConnection()->getCommandBuilder()->applyLimit($command->text,$limit,$offset);
}
foreach($this->params as $name=>$value)
$command->bindValue($name,$value);
return $command->queryAll();
}
Fetches the data from the persistent data storage.
fetchKeys()
method
|
protected array fetchKeys()
| ||
| {return} | array | list of data item keys. |
Source Code: framework/web/CSqlDataProvider.php#115 (show)
protected function fetchKeys()
{
$keys=array();
if($data=$this->getData())
{
if(is_object(reset($data)))
foreach($data as $i=>$item)
$keys[$i]=$item->{$this->keyField};
else
foreach($data as $i=>$item)
$keys[$i]=$item[$this->keyField];
}
return $keys;
}
Fetches the data item keys from the persistent data storage.
take care of keyField property
I had a problem using simple query that didn't have a column called 'id'. It's not mentioned here in documentation, but usesr MDomba wrote that field 'id' is by default used for kefField property. So, if your key column has different name then you need declare it.
One more thing, seems you aren't supposed to build query without a key column. Not a big disadvantage, but just good to be aware.
Signup or Login in order to comment.