How to write dependency for file cache

Yii 2.0 General Discussions
1

Hi all,

I am trying to figure out how I can set query cache to expire ( or update ) when value of some field in database change.

So if I am selecting number of employees that each company has, and I cache that query, I would like to update result returned by cache if number of employees change for any company.

Here is the table:

Company name - employees

Yii soft - 10

Trance soft - 20

Query that is cached will return the result of 30 when I do sum of all employees. But, if in Yii soft company, number employees change to 20, cached query should return 40. How can I achieve this, knowing that there is no update time field in this table ?

Here is my real query:




$duration = 60;


$dependency = [

    'class' => 'yii\caching\DbDependency',

    'sql' => "<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />",

];


$result = $db->cache(function ($db) {


    return $db->createCommand("SELECT floor(sum(variable_value)) as totalSum

                               FROM company_report

                               JOIN company          ON company_report.company_number = company.company_number 

                               JOIN users            ON company.id = users.company_id 

                               JOIN club             ON users.id = club.users_id

                               JOIN club_offer       ON club.id = club_offer.club_id

                               JOIN club_offer_cpv   ON club_offer.id = club_offer_id 

                                                     WHERE club_offer_cpv.cpv_id LIKE '03%'

                                                     AND company_report.variable_code = 'AOP 201'

                                                     AND company_report.report_year = 2013

                              ")->queryAll();

}, $duration, $dependency);


return $result;

Fields in real table are: id, company_name, report_year, variable_code, variable_value.

As you can see, I have tried using duration dependency, thinking that after duration of cache expire, cache mechanism will get new data and insert it into file where cached data is stored. But this is not happening. After duration expire, and user visit the same page again, he will get old cached data.

So, if values in database change, I want my cache to change too. How can I do this ? Thanks

2

Looks quite difficult, almost impossible to me. I would rather admit the design flaw and try to add ‘update_time’ columns to the existing tables before things get more difficult.

In order to use query cache, you should have some simple and light trick to check the changes in the db table. Without ‘update_time’ columns, you’ll end up executing a very complex and lengthy query to check the changes … it’s against the idea of caching.

As for the failure of cache expiration by the duration, I don’t know what’s wrong. Please try using the cache only with the duration (without a dependency) and see if it still fails or not.

3

Thanks, if I remove dependency it will work.

I am trying to understand concept of caching by expiration. If I am not wrong, all pages that are generated by cached query will be served from cache for a given duration, no matter who visit it ? If I visit some page before you, that page will be cached from that moment on, and if you come to visit it after me it will be served from cache ? In that case it would be smart to have some cron task that will trigger all site pages so the cache is triggered too, and if duration is set to 1 day, whole day pages will be served from cache. Is this possible somehow ?

4

Yes, I think you are right in understanding the expiration of the cache.

Cron job might be a good idea to update the cache content. I’ve never thought of it. In that case you may set the duration of the cache forever, and manually purge it before you update the content with the cron job.

Though, I’d rather want to encourage you to re-design the db table schema. Is there something that forbid you from doing it?

5

Boss

6

Fire him.

7

I saw in the guide that for fragment caching you can use this dependency:


'sql' => 'SELECT MAX(updated_at) FROM post',

Does this mean that data that is cached will be updated if any row in table whose updated_at field has changed ?

Can this be applied to query caching too ?

8

Yes, you are right. If the result of the sql has changed, the cached fragment will be invalidated.

Any cache dependency can be used for data cache. So it can also be applied to query cache, fragment cache or page cache, because they are all implemented on the basis of data cache.