CDbCacheDependency and CActiveDataProvider


I want to use DB cache dependency so if new item is added on current page, cache will refresh.

But how to set "sql" parameter on CDbCacheDependency when using CActiveDataProvider.

For example, in controller:

public function actionIndex()


	dataProvider=new CActiveDataProvider('Offers', array(


                'order'=>'date DESC',   






		$this->render('index', array('offers'=>$dataProvider));


In view:

$this->widget('zii.widgets.CListView', array(






Can I use cache in this view with DB dependency using DataProvider?


Also interested in this solution

To simplify the question:

Can I get the full sql query from DataProvider?

I am looking for this solution also, and couldn’t found it. Is there a way to do this?


There’s a wiki on that.

Using cache in CActiveDataProvider

I am not sure if that helps.

I have my criterias separately and should be part of the cache. Also my objects have a lot of relations, so I can’t quickly set the cache limit to just 2, as it might load a bunch of relations on multiple levels.

I’m not sure what you mean by that. Do you mean that you are going to retrieve some criteria from cache?

I think that we can set the cache limit to 2 as long as the eager loading is concerned no matter what complicated relations we might have. But, yeah, I agree that the lazy loading could be a headache. ;)

public function getEditionQuestions($edition_id) {

        // Warning: Please modify the following code to remove attributes that

        // should not be searched.

        $criteria = new CDbCriteria;

        $criteria->compare('edition_id', $edition_id, true);

        return new CActiveDataProvider($this, array(

                    'criteria' => $criteria,

                    'sort' => array(

                        'defaultOrder' => 't.`order` ASC',




I’m sorry, but I don’t understand. What’s your problem(question)?


Ah, so you are talking about CDbCacheDependency !!

You want to define the dependency BY a CActiveDateProvider, don’t you?

Well, I’m very sorry. I totally misunderstood the problem.

I don’t think it’s a good idea to define a CDbCacheDependency BY a CActiveDataProvider.

Actually I mean how to define a cache for the source code I posted.

When I have the logic of the query in the criteria. How would you rewrite that to be served from cache?

OK. It’s very easy. You just have to follow the wiki. :)

public function getEditionQuestions($edition_id) {

        $criteria = new CDbCriteria;

        $criteria->compare('edition_id', $edition_id, true);

        $dependecy = new CDbCacheDependency('SELECT MAX(update_time) FROM tbl_question');

        $duration = 60 * 60 * 24; // 1 day

        return new CActiveDataProvider(Question::model()->cache($duration, $dependency, 2), array(

                    'criteria' => $criteria,

                    'sort' => array(

                        'defaultOrder' => 't.`order` ASC',




In the above I’m assuming your model is ‘Question’ and your table is ‘tbl_question’ that has ‘update_time’ column.

It’s true that the criteria for this data provider is dynamically constructed. But it’s OK.

For instance, when you call this method with $edition_id set to ‘1234’, it will create a set of 2 cache IDs from the actual sqls: 1 for the count of rows and the other for the rows(results).

And when you call it with $edition_id set to ‘5678’, it will then create ANOTHER set of IDs.

The number of cache IDs will count up by 2 with each different $edition_ids.

In each case, if CActiveDataProvider has found an entry with the specified ID in the cache, and it is not outdated by the duration or the dependency, it will return the cached result. Otherwise it will execute the query and store the result in the cache before returning it.

I will try the above code, but I am not sure how you set the cache id, and where do you tell that each different $edition_id should be on different cache.

We set the ID for a cache entry by a string. The cache component will then generate an unique key for the given string and use it to identify the cache entry.

If given the same string, then it will be the same entry.

In query caching, the sql statement is treated as the ID string.

So if the user reorders the grid, that creates a new cache?

Also if the user paginates, that is a new cache?

Also when the user changes the grid filters that is a new cache?

So for the same table, we end up having N number of caches? And all of them will expire when the structure changes?

What if I want to cache for 1 hour and decide not to use db cache, then how will the order/pagination/filter stuff work?


Query caching is sometimes very expensive because of that.

If you have added a new record or modified a record, then all of them will become invalid by the specified dependency. … If it will not become invalid, it will be a disaster.

Um, I don’t understand your question.

for the last one, if I choose for dependency a time, eg 1 hour.

Then how is the cache ID composed?

Will that affect the order by and pagination?

Each cache entry has its own duration and dependency. The validity of the cache entry will be evaluated with its own duration and dependency when it gets accessed.

If the cache entry for page #1 has become invalid because of the duration, then that of page #2 will become invalid sooner or later, because they have the same duration and probably has been created almost at the same time.

If the cache entry for page #1 has become invalid because of the dependency change, then that of page #2 will become invalid exactly at the same time.

Please read the guide for the meaning of ID, duration and dependency of the cache.

Just a quick question - would you happen to know how to invalidate (flush) the cache if a record is deleted? I imagine I could add a CDbCacheDependency to monitor the row count on the database table (i.e.: the row count decreases by one upon deleting a record), but I’d rather be able to programmatically do the invalidation. Any thoughts?



Ah, yes, the deletion of rows are not handled correctly by

$dependecy = new CDbCacheDependency('SELECT MAX(update_time) FROM tbl_question');

It’s a headache. :(

twisted1919 has come up with a solution:

$dependecy = new CDbCacheDependency('SELECT AVG(update_time) FROM tbl_question');

Aha, cool idea! Thanks for posting!