Recently we’ve been working on a new project that requires caching of both views and database queries. One of the problems I came across I wanted to Result Cache an query I was using for a pager. This caused a couple of problems, one being I needed to be able to clear the cache by its prefix so we would never have a stale cache. Doctrine has a built in deleteByPrefix call for this, however on a pager how do I get it so that it will use a result cache, but still use different indexes for different pages? The following code would not work:
$this->createQuery('sc')
// A couple of complex joins here, etc
->where('sc.video_id = ?',$id)
->orderBy('sc.created_at DESC')
->useResultCache(true,sfConfig::get('app_comment_cache'),'comment_index');
Well here the problem is everything is being cached as as the ‘comment_index’ cache, so if you passed that query to a pager, and told it to be on the second page, it’d see the ‘comment_index’ cache exists, and use that. A simple way around this is:
// Query build...
->useResultCache(true,sfConfig::get('app_comment_cache'),'comment_index_'.$page);
In this example page is the parameter you are passing the query and the Doctrine pager to tell it what page cache to look at.
Then a very weird problem was occurring, I was getting more queries if I USED the cache than if I didn’t. Very weird. It seemed that one of the joins object did not seem to be getting stored in the cache. The join looked something like this:
$this->createQuery('sc')
->leftJoin('sc.User u')
->leftJoin('u.Profile p')
//.... more joins etc
The problem was the profile object was not getting stored in the result cache and thus causing a query each time it was called from the user object. After much hunting around, a long time in #doctrine, and a few leads from a couple of people, it turns out, by default, Doctrine will only serialize the immediate relation to the main object (in this case ‘sc’). However, you can make it so that it will serialize objects further down the line by overriding the function serializeReferences to return true in the class you want to serialize references from. In my example this is the User class. Since our application will never only need the ‘User’ class to be serialized on a result cache I completely overrode the function and made it always return true
class User extends BaseUser{
public function serializeReferences($bool=null)
{
return true;
}
}
Of course you can set this on a per object instance via $user->serializeReferences(true). Overriding the method the way I did you need to be careful as you could potentially waste a ton of storage space in your result cache.
Hope this saves someone some head banging and confusion on how using a cache could actually cause more queries if not stored properly.
