In my last post on this topic, I discussed the occasional need to make large SQL calls (for example, calculating the navigation structure of your site), and the relative lack of good options for reducing the number of times Cake would need to make those large calls.

We needed to create a basic caching function that would work to reduce the load on the SQL server for common find and findAll queries. We might as well make it a general function, so any model can use it. So let’s add this function to our app_model.php file, located in the /app folder of the Cake distribution. If the file doesn’t exist (by default it does not), you can create it.

I made two functions for data-caching: cachedFind and cachedFindAll. They take the same inputs as the standard Cake functions find and findAll, to retain any and all flexibility we need. Here’s the code:

< ?php
class AppModel extends Model{

function cachedFindAll($conditions = null, $fields = null, $order = null, $limit = null, $page = 1, $recursive = null)
{
$findID = null;
//construct the FindID
if (is_array($conditions)) {
$conditionsID = implode('.',$conditions);
} else {
$conditionsID = $conditions;
}

if (is_array($order)) {
$orderID = implode('.',$order);
} else {
$orderID = $order;
}

if (is_array($fields)) {
$fieldsID = implode('.',$fields);
} else {
$fieldsID = $fields;
}

$findID = md5($this->useTable . $conditionsID . $orderID . $fieldsID);

if (!$findID) {
return false;
}

//check to see if a cached version exists
$cachedSQLFile = CACHE . DS . 'models' . DS . $findID.'_data.php';

if (file_exists($cachedSQLFile)) {
//the file exists, so read the file and unserialize it.
if (function_exists('file_get_contents')) {
$serializedNav = file_get_contents($cachedSQLFile);
} else {
$serializedNav = implode('', file($cachedSQLFile));
}
$data = unserialize($serializedNav);
}

if (!isset($data) || !is_array($data)) {
//either we can't find the variable and it doesn't exist, or the data isn't an array, so reload it

$data = $this->findAll($conditions, $fields, $order, $limit, $page, $recursive);

//if it's there and the data was bad, delete the file
if (file_exists($cachedSQLFile)) {
@unlink(file_exists($cachedSQLFile));
}

//write the new, cached(!!) version
$cacheFile = fopen($cachedSQLFile,"w");
fwrite($cacheFile,serialize($data));
fclose($cacheFile);

}

return $data;

}

}
?>

I’ll go through the main points of this function:

$findID = null;
//construct the FindID
if (is_array($conditions)) {
$conditionsID = implode('.',$conditions);
} else {
$conditionsID = $conditions;
}

if (is_array($order)) {
$orderID = implode('.',$order);
} else {
$orderID = $order;
}

if (is_array($fields)) {
$fieldsID = implode('.',$fields);
} else {
$fieldsID = $fields;
}

$findID = md5($this->useTable . $conditionsID . $orderID . $fieldsID);

if (!$findID) {
return false;
}

Because we want flexibility here, we need to have a way to uniquely identify the cached data. The way I approach that is to create an MD5 - encoded string based on the input parameters of the findAll query, which associates a unique string with distinct queries. (A hat-tip to August Kaiser at Beehive Media for the recommendation to add the $this->useTable to ensure unique calls for each model).

$cachedSQLFile = CACHE . DS . 'models' . DS . $findID.'_data.php';

if (file_exists($cachedSQLFile)) {
//the file exists, so read the file and unserialize it.
if (function_exists('file_get_contents')) {
$serializedNav = file_get_contents($cachedSQLFile);
} else {
$serializedNav = implode('', file($cachedSQLFile));
}
$data = unserialize($serializedNav);
}

Now we create a link to where the cached file *should* be, using Cake’s defined CACHE location, the model directory, and then the fileID we created above, with the suffix of “_data.php”. If the file is found, the function attempts to read the contents of the file and unserialize it. The function check exists because file_get_contents isn’t standard until PHP 4.3 and up.

if (!isset($data) || !is_array($data)) {
//either we can't find the variable and it doesn't exist, or the data isn't an array, so reload it

$data = $this->findAll($conditions, $fields, $order, $limit, $page, $recursive);

//if it's there and the data was bad, delete the file
if (file_exists($cachedSQLFile)) {
@unlink(file_exists($cachedSQLFile));
}

//write the new, cached(!!) version
$cacheFile = fopen($cachedSQLFile,"w");
fwrite($cacheFile,serialize($data));
fclose($cacheFile);

}

Next we check if the unserialized data exists, and if does, if it’s an array. If either of those conditions are not met, we go and retrieve the data from the database, try to delete an old copy of the file if it exists, and serialize the new data, and write it to the model cache directory for next time.

There’s one more step we need to do before this function becomes completely useful. In the same app_model.php file, we need to add one more function:

function afterSave()
{
clearCache('_data', 'models', '.php');
return true;
}

AfterSave() is a Cake-specific callback function that is executed after data is inserted into the model. What this function does, then, is clear out all the cached versions of our find calls every time we update the database, in order to make sure the user sees the most recent version of the data. Since we put this code into the AppModel class, it runs everytime we save something from ANY model in our system. This may not be the best implementation for web apps that have user-updated content areas, but for a single or multiuser CMS such as our WebTree system, it works quite nicely.

Now anytime you want to have Cake cache the returned data from a SQL call, you can use the cachedFindAll function exactly like the standard findAll function:

< ?php
$data = $this->Model->cachedFindAll(array('Model.displayed'=>'1'),null,array('Model.date'=>'DESC'));
?>

In case you’re curious, the cachedFind function is modeled exactly like the find function in Cake, where it just grabs the first array of data from a findAll call and returns that:

function cachedFind($conditions = null, $fields = null, $order = null, $recursive = null)
{
$data = $this->cachedFindAll($conditions, $fields, $order, 1, null, $recursive);
if (empty($data[0])) {
return false;
}
return $data[0];

}

Now you have two functions that will drastically reduce the amount of SQL calls made by your app. Enjoy.