CakePHP's default cacheQuery functionality is good enough for caching SQL queries for a single request. But in order to have a persistent query cache, you have to write your own code. Recently, we had this requirement where we had to cache every individual query.
A quick search on Google lead me to this post where Miles Johnson has provided a way to cache individual queries by overriding the Model's find() function. Our's was a similar requirement with the exception that we wanted to save the cached data in separate folders of our choice so that while deleting the cache, we could delete only the affected cached data instead of deleting the whole cache. So, we modified the code from Miles and also added a function to delete the cache from given folder.
So, let us start with overriding the find() function in our app_model.php.
-
$key = $fields['cache'];
-
$expires = '+1 day';
-
$conf = 'sql_cache';
-
-
$key = $fields['cache'][0];
-
-
$conf = $fields['cache'][1];
-
}
-
}
-
-
// Set cache settings
-
'duration' => $expires
-
));
-
-
// Load from cache
-
$results = Cache::read($key, $conf);
-
-
$results = parent::find($conditions, $fields, $order, $recursive);
-
Cache::write($key, $results, $conf);
-
}
-
-
return $results;
-
}
-
-
// Not cacheing
-
return parent::find($conditions, $fields, $order, $recursive);
-
}
The above function requires you to create a sql_cache folder in app/tmp and a folder structure of your choice inside it so that you can cache data in separate folders. For ex., in our case, we wanted to store the category, form, blog and blog comments cache data individually. So we created those many folders inside sql_cache folder. Remember to give writable permission to all the folders you create.
Next, open app/config/core.php and add the following to it
-
'engine' => 'File',
-
'path' => CACHE .'sql_queries'. DS,
-
'serialize' => true,
-
));
You are now all set to start using the brand new query cache. But for that, you will have to modify your existing calls to find() that you want to start caching. Add a "cache" key in your options array to find() function. The "cache" array can accept two parameters -
- Unique key - A file with this name will be created in the folder you ask for
- Folder Path - The cache file mentioned above will be looked for in the folder path given here.
Following are some of the examples to show how this technique can be used in various situations.
Simple find by id
Cache categories by agent
The above query will generate different cache files depending on the agent selected. Feel free to make this unique key as complex as possible and you can cache most of the data. One such example is pagination.
-
// Added this code to cache query
-
$cache = array('pagination-'.implode('_', array_keys($order)).'-'.implode('_', $order).'-'.$catStr.'-'.$page, 'sql_cache_forms_pagination');
-
-
$this->find('all', compact('cache', 'conditions', 'fields', 'order', 'page', 'limit', 'recursive', 'extra'));
From the above example you can see that we are caching records for every page and various sort orders as well.
Deleting a particular cache file
As I mentioned earlier, we have set the cache expiry to a longer period as data is not changing frequently. So, we wrote another function in app_model to delete only the affected portion of cache when something changes. The function goes as follows -
-
function delete_cache_data($name = null, $conf = 'sql_cache') {
-
));
-
-
if ($name) {
-
Cache::delete($name, $conf);
-
} else {
-
Cache::clear(false, $conf);
-
}
-
}
The function takes two parameters that we pass to "cache" array in find options. The function will simply delete the file given in $name from the path given in $conf. If you want to remove all the cached data, pass null for $name. So now, if you change the visibility of a Category, you can simply clear the category cache with a following call -
-
$this->Category->delete_cache_data(null, 'sql_cache_category');
Comments, feedback and suggestions are most welcome.
Your code has been successfully stolen to our project!
Thanks man.
Glad to know that it was useful for you.
Hi, I tried modifying milesj code but i never got Folder Path to work- 'sql_cache_layout'.
Also cache key does not accept two parameters using
----------------------------------------
if (isset($fields['cache'][1])) {
$conf = $fields['cache'][1];
}
----------------------------------------
$conf changed back to $expires allows two parameters but file saves to parent sql_qeries folder.
Thanks.
Ok got it.. the code was incomplete..
So inside the if statement-
--------------------------------------------
$key = $fields['cache'];
$expires = '+1 day';
$path = null;
$conf = 'sql_cache';
// required settings $key['name'] $path['location']
if (is_array($fields['cache'])) {
$key = $fields['cache'][0];
$path = $fields['cache'][1];
//optional setting for expiration time
if (isset($fields['cache'][2])) {
$expires = $fields['cache'][2];
}
}
// Set cache settings
Cache::config($conf, array(
'prefix' => strtolower($this->name) .'-',
'path' => CACHE .'sql_queries' . DS . $path,
'duration' => $expires
));
----------------------------------------------------------------------
I added $path and kept $expires. I also moved 'path' from core config script into the find() function config.
I call it like-
-------------------------------------------------------------------------------
'cache' => array('navigation_query', 'layout_folder', '+1 week')
-------------------------------------------------------------------------------
Thanks