Yii- Caching with CSqlDataprovider

Question

Is it possible to do caching of data from sql server queries when using CSqlDataProvider. If so can anyone please provide some links for documentation about it. Or if you have done it personally please guide.

I did a search but found nothing :(

Answer 1

There is some example of implementing this feature

<?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;
        }
}
?>