Hello,
I’m building a large-scale website using Yii. I don’t want to use ActiveRecord on the main content list page because of performance issue. but if I use DAO, how to deal with the pagination in Model? return an array with total item count and list data? or return a CSqlDataProvider object? maybe CSqlDataProvider is better，but how to use cache with it?
Thanks in advance!
Sorry for my poor English 
I couldn’t find anything about it, so I’ve editted CSqlDataProvider and renamed it to CachedSqlDataProvider.
I’ve simply added 2 public variables ($queryCache and $queryCacheLife) in the class and edited fetchData method.
CSqlDataProvider.php
<?php
....
$command=$db->createCommand($sql);
foreach($this->params as $name=>$value)
$command->bindValue($name,$value);
...
?>
CachedSqlDataProvider.php
<?php
...
if( $this->queryCache == true && is_numeric($this->queryCacheLife) && $this->queryCacheLife > 0 )
$command=$db->cache( $this->queryCacheLife )->createCommand($sql);
else
$command=$db->createCommand($sql);
foreach($this->params as $name=>$value)
$command->bindValue($name,$value);
...
?>
Now you can use it like this;
<?php
$dataProvider = new CachedSqlDataProvider( $sql, array( 'queryCache' => true, 'queryCacheLife' => 300 ) );
?>
Here is the full class file
<?php
class CachedSqlDataProvider extends CDataProvider
{
public $queryCache;
public $queryCacheLife;
/**
* @var CDbConnection the database connection to be used in the queries.
* Defaults to null, meaning using Yii::app()->db.
*/
public $db;
/**
* @var string the SQL statement to be used for fetching data rows.
*/
public $sql;
/**
* @var array parameters (name=>value) to be bound to the SQL statement.
*/
public $params=array();
/**
* @var string the name of key field. Defaults to 'id'.
*/
public $keyField='id';
/**
* Constructor.
* @param string $sql the SQL statement to be used for fetching data rows.
* @param array $config configuration (name=>value) to be applied as the initial property values of this class.
*/
public function __construct($sql,$config=array())
{
$this->sql=$sql;
foreach($config as $key=>$value)
$this->$key=$value;
}
/**
* Fetches the data from the persistent data storage.
* @return array list of data items
*/
protected function fetchData()
{
$sql=$this->sql;
$db=$this->db===null ? Yii::app()->db : $this->db;
$db->active=true;
if(($sort=$this->getSort())!==false)
{
$order=$sort->getOrderBy();
if(!empty($order))
{
if(preg_match('/\s+order\s+by\s+[\w\s,]+$/i',$sql))
$sql.=', '.$order;
else
$sql.=' ORDER BY '.$order;
}
}
if(($pagination=$this->getPagination())!==false)
{
$pagination->setItemCount($this->getTotalItemCount());
$limit=$pagination->getLimit();
$offset=$pagination->getOffset();
$sql=$db->getCommandBuilder()->applyLimit($sql,$limit,$offset);
}
if( $this->queryCache == true && $this->queryCacheLife > 0 )
$command=$db->cache( $this->queryCacheLife )->createCommand($sql);
else
$command=$db->createCommand($sql);
foreach($this->params as $name=>$value)
$command->bindValue($name,$value);
return $command->queryAll();
}
/**
* Fetches the data item keys from the persistent data storage.
* @return array list of data item keys.
*/
protected function fetchKeys()
{
$keys=array();
foreach($this->getData() as $i=>$data)
$keys[$i]=$data[$this->keyField];
return $keys;
}
/**
* Calculates the total number of data items.
* This method is invoked when {@link getTotalItemCount()} is invoked
* and {@link totalItemCount} is not set previously.
* The default implementation simply returns 0.
* You may override this method to return accurate total number of data items.
* @return integer the total number of data items.
*/
protected function calculateTotalItemCount()
{
return 0;
}
}
?>