How to use Doctrine DBAL Cache with Symfony2

In Symfony, DoctrineBundle is responsible for the configuration and integration of Doctrine, including DBAL. Nonetheless, the bundle does not yet support DBAL cache configuration (but support it for the ORM part of Doctrine).

You have to create a CompilerPass, inside one of your own bundle, whose job will be to retrieve the dbal service definition and add the needed configuration to be able to use the cache feature.

<?php
// src/Acme/HelloBundle/DependencyInjection/Compiler/AddDbalCacheConfigurationPass.php

namespace Acme\HelloBundle\DependencyInjection\Compiler;

use Symfony\Component\DependencyInjection\Compiler\CompilerPassInterface;
use Symfony\Component\DependencyInjection\ContainerBuilder;
use Symfony\Component\DependencyInjection\Reference;

class AddDbalCacheConfigurationPass implements CompilerPassInterface
{
	public function process(ContainerBuilder $container)
	{
		$id = 'doctrine.dbal.default_connection.configuration';

		if ($container->hasDefinition($id)) {
			$container
				->getDefinition($id)
					->addMethodCall('setResultCacheImpl', array(new Reference('doctrine.orm.default_result_cache')))
			;
		}
	}
}

As you can see in the Doctrine documentation, in order to configure DBAL cache, you have to call the « setResultCacheImpl » method on your dbal connection configuration object, and give it the cache driver you want to use. In our case, we give a Reference to a service to this method. This service is created by DoctrineBundle for the ORM cache, the trick here is that we are using the same configured cache service to simplify a little.

So to configure the cache driver, it’s exactly like for the ORM :

// app/config/config.yml

doctrine:
    orm:
        query_cache_driver: apc

Then, you add your CompilerPass to the ContainerBuilder in your bundle main class :

<?php
// src/Acme/HelloBundle/AcmeHelloBundle.php

namespace Acme\HelloBundle;

use Symfony\Component\HttpKernel\Bundle\Bundle;
use Symfony\Component\DependencyInjection\ContainerBuilder;
use Kreatys\Ie3Bundle\DependencyInjection\Compiler\AddDbalCacheConfigurationPass;

class AcmeHelloBundle extends Bundle
{
	public function build(ContainerBuilder $container)
	{
		parent::build($container);
	
		$container->addCompilerPass(new AddDbalCacheConfigurationPass());
	}
}

Finally, you just have to do a classic DBAL query and use a QueryCacheProfile when executing your query :

<?php
// src/Acme/HelloBundle/Repository/MyEntityRepository.php

// ...
                $lifetime = 1800;
		$params = array('myParam' => 'myValue');
		$conn = $this->getEntityManager()->getConnection();
		$qb = $conn->createQueryBuilder();
		$qb
			->select('name')
			->from('myTable', 't')
			->where('something = :myParam')
		;

		$query = $qb->getSql();
		$stmt = $conn->executeQuery($query, $params, array(\Doctrine\DBAL\Connection::PARAM_STR_ARRAY), new QueryCacheProfile($lifetime, 'my_cache_key'));
		
		$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);	
		$stmt->closeCursor(); // very important, do not forget
// ...

(Version used : 2.3)