CakePHP 3 - Cache query keyed by full SQL statement

0
votes

Using this answer I tried added caching to a query that is particularly slow.

This query has fixed selected fields and limits but three components can differ between different users: the where statement on the table itself, on filters on a joined table and on filters on a second joined table.
So I filter data on the main table and two joined ones.

For caching to work I need to incorporate the filtered conditions into the cache-key. How can I extract all the find filters, including on joins, for this purpose?

If I use $q->clause('where'), like in the linked suggestion, I only get the filter on the main table, not the joins. And $q->clause('join') is empty.

$query = $this->Regions->find('veryComplicatedFinder')
    ->cache(function (\Cake\ORM\Query $q)
            {
                debug($q->clause('where'));
                return 'test123';
            }, 'queries');

I figured I might just use the entire SQL statement. However, $q->sql() results in something like:

'SELECT ... FROM regions Regions INNER JOIN chains Chains ON (Chains.id in (:c0,:c1,:c2) AND Chains.id = (Regions.chain_id)) LEFT JOIN stores Stores ON (Stores.type in (:c388) AND Stores.type in (:c389) AND Regions.id = (Stores.region_id)) LEFT JOIN sales Sales ON Stores.id = (Sales.store_id) WHERE (Regions.shape && ST_MakeEnvelope(51.5,5,52,5.5,4326)) GROUP BY Regions.id LIMIT 2000'

It seems the binding parameters are not included.

1
cachingcakephpparametersormcakephp-3.0

1 Answers

2
votes

Joins for containments are built lazily

If the join clause is empty, then you're probably using containments, ie the contain(), *joinWith(), matching(), and/or notMatching() methods.

Joins of containments will only be added to the query object's join clause when the query is being compiled, until then they live in the eager loader, with their query builder callbacks not yet evaluated. So while you can obtain them like this:

// contain()
$contain = $query->getEagerLoader()->getContain();

// *joinWith()/matching()/notMatching()
$matching = $query->getEagerLoader()->getMatching();

the conditions haven't yet been applied anywhere.

Obtaining bindings

That being said, if you'd wanted to evaluate the whole query, then you could access the bound values via the value binder, like this:

$sql = $query->sql();
$bindings = $query->getValueBinder()->bindings();

Building cache keys in finders

One approach you might want to consider is partially shifting the cache key responsibility into the finder, ie building cache keys in your finders where you have all the required information at hand, and setting them on the query object to evaluate/use in other places.

It could look something like this:

public function findVeryComplicatedFinder(\Cake\ORM\Query $query)
{
    $cacheKey = '';

    if ($abc) {
        $cacheKey .= 'abc';
        $query->leftJoinWith('Abc', function () {
            // ...
        });
    }

    if ($xyz) {
        $cacheKey .= 'xyz';
        $query->innerJoinWith('Xyz', function () {
            // ...
        });
    }
    
    // ...

    $query->applyOptions([
        'veryComplicatedFinderCacheKey' => $cacheKey
    ]);
    
    return $query;
}

In your main query object's cache() callback you could then read that option and use it to build the final cache key, something along the lines of this:

$query = $this->Regions
    ->find('veryComplicatedFinder')
    ->cache(
        function (\Cake\ORM\Query $query) {
            $options = $query->getOptions();

            $cacheKey = 
                'mainQueryCachKeyBasedOnWhateverYouRequire' .
                $options['veryComplicatedFinderCacheKey'];

            return $cacheKey;
        },
        'queries'
    );