How to cache a Doctrine 2 query properly on Symfony?

Question

I have been reading extensively about Doctrine different options for Caching as well as symfony caching mechanisms:

Symfony Official: https://symfony.com/doc/4.0/components/cache.html

Doctrine Official: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/caching.html

KnP university (very useful as always): https://knpuniversity.com/screencast/symfony-fundamentals/caching

Should I use Doctrine Cache or Symfony 4 cache? Which one to choose from?

I have a large amount of data to pull from my database that I’d like to cache (pulling entities with tons of left joins).Those left joins are, for some, updated every hour, every day or every minutes on regular basis though a bot called with a cron job (symfony command).

/**
 * @return Coins[] Returns an array of Crypto objects
 */

public function findOneByTickerRelationnal($ticker)
{
    $em = $this->getEntityManager();
    $updatesrepository = $em->getRepository(Updates::class);
    $updates = $updatesrepository->findOneBy(['id'=> 1 ]);

    // This is where I’ve been doing additional work to limit my left join as much as possible with a ‘with’ on left join
    $recentMarkets = $updates->getMarket();
    $recentPrices = $updates->getPrice();
    $recentSources = $updates->getSources();

    $cryptos = $this->createQueryBuilder('c')
        ->select('partial c.{id, name, ticker}’) //<= use of partial is a plus but you need to know exactly which fields you want
        ->leftJoin('c.prices', 'p','WITH', 'p.last_updated >= :recentPrices')
        ->addSelect('partial p.{id, price_usd, daily_volume_usd, change_1h, change_1d, change_7d, rank}')
        ->leftJoin('c.markets', 'm','WITH', 'm.last_updated >= :recentMarkets')
        ->addSelect('partial m.{id, cur_supply, market_cap, max_supply}')
        ->leftJoin('c.sources', 's','WITH', 's.last_updated >= :recentSources')
        ->addSelect('s')
        ->where('c.ticker = :ticker')
        ->setParameter('recentPrices', $recentPrices)
        ->setParameter('recentMarkets', $recentMarkets)
        ->setParameter('recentSources', $recentSources)
        ->setParameter('ticker', $ticker)
        ->getQuery()
        ->getArrayResult(); //<=Changes everything 

    $results = $cryptos[0];

    return $results;
}

I’m not sure How to cache this query properly considering the frequent updates.

Thanks

Flying Flying · Accepted Answer · 2018-08-12T11:15:28

Doctrine have several types of caches:

for class metadata (where class mappings to database are stored)
for queries (where parsed DQL queries are stored)
for query results (where actual results fetched by queries are stored)

First 2 types of Doctrine caches are not relevant for your question because they're specific to Doctrine and there is no direct alternatives for them in Symfony. Query results cache can be (indirectly) replaced with Symfony cache.

Use of query result cache and decision about which cache to use should depend on your application's logic. You can prefer to use Doctrine query results cache in a case if following criteria is met:

You need to store raw data fetched from database (means it requires no further processing before being stored in cache)
Your query results can be stored in cache for specific amount of time and unlikely to became stale within this period of time

In this case Doctrine query results cache can be useful for you because it will be transparent for your application. It may be useful just for some queries since you can control Doctrine cache usage and lifetime on per-query basis.

In a case if you need to apply some further processing for results fetched from database before storing them in cache or you have some additional logic to apply to decide if cache contents became stale - it is better to use Symfony cache because it is specific for your application and can be controlled by you.

How to cache a Doctrine 2 query properly on Symfony?

1 Answers