system system.base system.caching system.caching.dependencies system.collections system.console system.db system.db.ar system.db.schema system.db.schema.cubrid system.db.schema.mssql system.db.schema.mysql system.db.schema.oci system.db.schema.pgsql system.db.schema.sqlite system.gii system.i18n system.i18n.gettext system.logging system.test system.utils system.validators system.web system.web.actions system.web.auth system.web.filters system.web.form system.web.helpers system.web.renderers system.web.services system.web.widgets system.web.widgets.captcha system.web.widgets.pagers zii.behaviors zii.widgets zii.widgets.grid zii.widgets.jui

CSqlDataProvider

system.web
继承 class CSqlDataProvider » CDataProvider » CComponent
实现 IDataProvider
可用自 1.1.4
源码 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.

公共属性

隐藏继承的属性

属性类型描述被定义在
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

公共方法

隐藏继承的方法

方法描述被定义在
__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

受保护的方法

隐藏继承的方法

方法描述被定义在
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

属性详情

db 属性
public CDbConnection $db;

the database connection to be used in the queries. Defaults to null, meaning using Yii::app()->db.

keyField 属性
public string $keyField;

the name of key field. Defaults to 'id'.

params 属性
public array $params;

parameters (name=>value) to be bound to the SQL statement.

sql 属性
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.

方法详情

__construct() 方法
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.
源码: framework/web/CSqlDataProvider.php#64 (显示)
public function __construct($sql,$config=array())
{
    
$this->sql=$sql;
    foreach(
$config as $key=>$value)
        
$this->$key=$value;
}

Constructor.

calculateTotalItemCount() 方法
protected integer calculateTotalItemCount()
{return} integer the total number of data items.
源码: framework/web/CSqlDataProvider.php#138 (显示)
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() 方法
protected array fetchData()
{return} array list of data items
源码: framework/web/CSqlDataProvider.php#75 (显示)
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() 方法
protected array fetchKeys()
{return} array list of data item keys.
源码: framework/web/CSqlDataProvider.php#115 (显示)
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.