Doctrine result cache not caching a query with a join

Question

I'm using Doctrine 2.2.2 trying to do a query for a user entity along with the user's company. I want this query to be cached since the data inside won't change often, if at all. Below is what I've tried:

$user = $em->createQuery('
    SELECT u, c
    FROM Entities\User u
    LEFT JOIN u.company c
    WHERE u.id = :id
')->setParameter('id', $identity)->useResultCache(true, 21600, 'user_' . $identity)->getResult();

Seemingly, the user entity is cached, but it still does a query for the company. Is there a way I can fetch both at the same time and have them be placed into the result cache?

I did find this, but it's very old and his solution did not work for me.

It was suggested to me that I do this, but that kind of defeats the purpose of the result cache.

Answer 1

I've tested this with Doctrine 2.3 using:

$q->setResultCacheDriver( new \Doctrine\Common\Cache\ApcCache() )
  ->setResultCacheLifetime( 21600 )
  ->setResultCacheId( 'user_' . $identity );

When I run it the first time, I see the actual query in my sql-logger.
When I run it the second, third, etc time, I don't see anything in my sql-logger.

My conclusion is it's working fine.

I guess you either haven't got a result-cache configured, or you have a non-permanent result-cache (like ArrayCache) configured, or it's a bug in Dontrine 2.2.2 (although I can't find anything about it on jira).

PS: I gather from another question (Doctrine detaching, caching, and merging) that you've upgraded to Doctrine 2.3. Do you still have this problem?

PPS: Somewhere after Doctrine 2.0 (I think it was 2.2), the result-cache changed. In stead of caching the hydrated result, the sql-result is cached (and hydration takes place every run). If you want to cache the hydrated result, you'll have to use the hydration-cache (but be ware that those results are not merged into the EntityManager).