Yii CActiveDataProvider query caching not working correctly

Question

I want to cache the DB results for a CActiveDataProvider instance.

I prepare the data provider in a controller's action, and then I use the data provider in a CGridView, later in some view.

I used to have this code:

$cars=new CActiveDataProvider(
    'Car'
    ,
    array(
        'criteria'=>array(
            'condition'=>'brand_id=:brand_id',
            'params' => array(':brand_id'=>$model->id),
            'order' => 'price',
        ),
        'pagination'=>false,
    )
);

and, following the tutorial in the yii wiki, I changed it to:

$cars=new CActiveDataProvider(
        Car::model()->cache(3600)
        ,
        array(
            'criteria'=>array(
                'condition'=>'brand_id=:brand_id',
                'params' => array(':brand_id'=>$model->id),
                'order' => 'price',
            ),
            'pagination'=>false,
        )
    );

but to no avail: the query is not being cached.

Answer 1

One of the advantages of CActiveDataProvider is that the query is NOT performed right away. The query is performed only when you use the CActiveDataProvider later on, say in a CGridView, that will call CActiveDataProvider::fetchData eventually triggering the query.

This is useful because you can use fragment caching, and you don't want to load data in the controller only to find out that you don't need it, because the fragment is cached.

This is exactly what happens here: the ActiveRecord ->cache() method instructs the DB Connection to cache the sequent query, but if the query is not immediately executed, some other query could be performed before this one, and this caching will not work.

I solved the problem creating a personalized ActiveDataProvider that will set the model cache just before the query taking place:

class CachedActiveDataProvider extends CActiveDataProvider
{

    public $cache_duration = null;
    public $cache_dependency = null;

    /**
     * The cache mechanism works by telling the DB Component to cache the next query.
     * When  fetchData() is called, the DB will cache the next query. 
     * There is a possibility that fetchData call calculateTotalItemCount()
     * and in that function, if this bit is active, we will tell the DB to cache
     * 2 queries, the count, and the actual fetch that will come next.
     * (if we don't do this, the DB will cache the count query, and will forget about
     * the instruction to cache the fetch query)
     * 
     * @var boolean
     */
    private $fetching_data = false;

    protected function fetchData()
    {
        if (!is_null($this->cache_duration ))
        {
            $this->model->cache($this->cache_duration, $this->cache_dependency);
        }

        $this->fetching_data = true;
        $ret = parent::fetchData();
        $this->fetching_data = false;

        return $ret;
    }

    protected function calculateTotalItemCount()
    {
        if (!is_null($this->cache_duration ))
        {
            $this->model->cache(
                $this->cache_duration, 
                $this->cache_dependency, 
                $this->fetching_data ? 2 : 1 //if fetching data, cache 2 queries: this count and the sequent fetching
            );
        }

        return parent::calculateTotalItemCount();
    }
}

I can now call it using

$cars=new CachedActiveDataProvider(
    'Car'
    ,
    array(
        'criteria'=>array(
            'condition'=>'brand_id=:brand_id',
            'params' => array(':brand_id'=>$model->id),
            'order' => 'price',
        ),
        'pagination'=>false,
        'cache_duration' => 3600,
    )
);