Versions
Look up a class, method, property or event

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:
$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

Hide inherited properties

PropertyTypeDescriptionDefined 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

Hide inherited methods

MethodDescriptionDefined 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

Hide inherited methods

MethodDescriptionDefined 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.

Total 1 comment

#1973 report it
barbq at 2010/10/23 07:33am
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.

Leave a comment

Please to leave your comment.