1. Context
2. Resolving the _MAX(updatetime) problem
3. Taking things further, Caching pages with multiple models

Note: Before reading this wiki you should have read the caching part in the Yii guide

Context ¶

I have a website whith a lot of visitors and a poor DB server. This is why I strongly needed to use some caching technique! The first thing I thought about was to use the CDbCacheDependency along with a request selecting the maximum update time of my table as shown in the Yii guide:

$dependency = new CDbCacheDependency('SELECT MAX(update_time) FROM table_a ');
$rows = Yii::app()->db->cache(1000, $dependency)->createCommand($sql)->queryAll();

The main problem of this request is it doesn't get the modifications when we delete a row: If the deleted row isn't the maximum update time then MAX(update_time) won't change and the cache won't be refreshed! Another problem is that the MAX request performance decreases on really big tables.

Resolving the _MAX(updatetime) problem ¶

The Database ¶

To resolve this problem we can use SQL triggers and another table. We are going to store the modifications of the different tables as rows in a new table.

So let's create the table:

[mysql]
CREATE TABLE `last_table_modifications` (
  `table_name` varchar(50) NOT NULL,
  `last_modification` datetime DEFAULT NULL,
  PRIMARY KEY (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then for each table we want to track we are going to create a row in this table:

[mysql]
INSERT INTO  `last_table_modifications` (
 `table_name` , `last_modification`
) VALUES ('table_a', NOW( )), ('table_b', NOW( ));

The last step on the Db side is to create 3 triggers for each table that are going to be tracked. Those triggers are going to update the modification table on : INSERT, UPDATE and DELETE:

[mysql]
DELIMITER $$
CREATE TRIGGER `trigger_table_a_delete` AFTER DELETE ON `table_a` FOR EACH ROW BEGIN
  UPDATE last_table_modifications SET last_modification=NOW() WHERE table_name="table_a";
END$$
CREATE TRIGGER `trigger_table_a_update` AFTER UPDATE ON `table_a` FOR EACH ROW BEGIN
  UPDATE last_table_modifications SET last_modification=NOW() WHERE table_name="table_a";
END$$
CREATE TRIGGER `trigger_table_a_insert` AFTER INSERT ON `table_a` FOR EACH ROW BEGIN
  UPDATE last_table_modifications SET last_modification=NOW() WHERE table_name="table_a";
END$$
DELIMITER ;

The php side ¶

Now each time we add a new model to the db, update one or delete it the changes will be tracked in last_table_modifications We can set the new dependency as:

$dependency = new CDbCacheDependency('SELECT last_modification FROM last_table_modifications WHERE taable_name="table_a"');
$rows = Yii::app()->db->cache(1000, $dependency)->createCommand($sql)->queryAll();

Of course we can also use it for fragment caching!

...other HTML content...
<?php if($this->beginCache($id, array('dependency'=>array(
        'class'=>'system.caching.dependencies.CDbCacheDependency',
        'sql'=>'SELECT last_modification FROM last_table_modifications WHERE taable_name="table_a"')))) { ?>
...content to be cached...
<?php $this->endCache(); } ?>
...other HTML content...

Taking things further, Caching pages with multiple models ¶

Then I came across another problem: I have some pages with a lot of differents models (some pages have more than ten, but let's say ten). To cache the data yii had to perform ten queries, one for each model type, and for me that's too much since all the requests are pretty similars.

This is why I decided to go with the following workflow:

• Before the action is executed I get the last_table_modifications rows and store it
• I change the CDbDependency to a CExpressionDependency and give it the data that I stored accordingly to the model type.

Storing the retrieved data ¶

The data that I'm going to store are needed during the time of the request, but there is no need it perdure during time. This is why I'm going to use Yii built-in param to store them. To take car of the case our request to fetch modifications times failed we can initate the params in the config file by setting:

'params' => array(
    ...
    'lastModifications' => array(
	    'table_a' => 0,
		'table_b' => 0,
	),
	....
),

Creating the filter ¶

Now we need to create a filter that is going to be executed before the controller actions. In my case I created it in the Controller class, the class all my other Controllers inherit from.

public function filterCacheInit($filterChain)
{
	$caches = Yii::app()->db->createCommand()
		->select('table_name, last_modification')
		->from('last_table_modifications')
		->queryAll();
	$cachesArray = array();
	foreach ($caches as $key => $cache) {
		$cachesArray[$cache['table_name']] = $cache['last_modification'];
	}
	//Note I'm using an intermediate array because Yii::app()->params
	// is a virtual attribute so you can't assign data as we are doing above
	Yii::app()->params["lastModifications"] = $cachesArray;
	$filterChain->run();
}

Now that we defined the filter we can apply it to the desired actions by setting:

public function filter()
{
	return array(
		'cacheInit',
		....
	); 
} 

If you want the filter to apply to only some actions you can add the desired actions after a + in the filter declaration 'cacheInit + edit, create'. If you want the filter to apply to all actions except some you can add the desired action after a - in the filter declaration 'cacheInit - delete'.

In my case I declared it int the Parent Controller class so all the inherited class have this cache filter (I'm using cache on almost all of my pages).

Using the news dependency ¶

Now that we have stored the modfications time, we just have to call it in the CExpressionDependency:

$dependency = new CExpressionDependency('Yii::app()->params["lastModifications"]["table_a"]');
$rows = Yii::app()->db->cache(1000, $dependency)->createCommand($sql)->queryAll();

A big advantage of using Yii::app()->params is that you can get it from almost everywhere in your app so you can use it:

• In the Controllers
• in the Views
• In the models
• In the widgets
• ...

This wiki was inspired by a post on the forum from redguy and talking about triggers and creating a table to handle update times.

Please don't hesitate to ask some question or comment on the flaw of this solution, I'm always looking for ways to improve the code of my application!