How to Cache with QueryBuilder and Paginator in Phalcon?

Question

I have a question about Cache in Phalcon. It is connected with Pagination and QueryBuilder. So, i will give u example:

$builder = $this->modelsManager->createBuilder()
    ->columns('a.*, m.*')
    ->addFrom('Models\Articles', 'a')
    ->leftJoin('Models\Multimedia', "m.parent_id=a.id AND m.is_default=1 AND m.subtype='pictures' AND m.type={m_type:str}", 'm')
    ->where('a.i18n={i18n:str} AND a.is_active_from IS NOT NULL AND a.is_active_from <= {today:str}', [
        'i18n' => $this->session->i18n, 
        'm_type' => 'Models\Articles',
        'today' => date("Y-m-d H:i:s")
    ])
    ->orderBy('a.is_accent DESC, a.date DESC, a.id DESC');

$paginator = new \Phalcon\Paginator\Adapter\QueryBuilder([
    "builder" => $builder,
    "limit" => 33,
    "page" => $this->request->getQuery('page', 'int')
]);

$this->view->page = $paginator->getPaginate();

Now... if we skip the part with query parameters and clauses... so... my question is:

HOW to add cache to query here?

In Phalcon documentation i can add ->cache only with getQuery function, but in my case- there is no such thing here.

So, the question remain: how can i use cache in that situation?

Thanks

Boris Delev · Accepted Answer · 2016-10-02T19:15:34.410

Well... after a weeks of thinking xD ... i find some good answer for my issue.

First we need to have cache service in Phalcon:

$di->set('dataCache', function() use ($config) {
    $lifetime = 60*60; // 1 hour
    if ($config->development) {
        $lifetime = 60; // 1 min
    }

    $frontCache = new \Phalcon\Cache\Frontend\Data([
        "lifetime" => $lifetime
    ]);

    $cache = new \Phalcon\Cache\Backend\File($frontCache, [
        "cacheDir" => $config->application->cache_dir."data/"
    ]);

    return $cache;
});

Now go in controller, where is my pagination (you can compare from first post):

     $builder = $this->modelsManager->createBuilder()
        ->columns('a.id, a.slug, a.is_accent, a.type, a.title, m.*')
        ->addFrom('Models\Articles', 'a')
        ->leftJoin('Models\Multimedia', "m.parent_id=a.id AND m.is_default=1 AND m.subtype='pictures' AND m.type={m_type:str}", 'm')
        ->where('a.i18n={i18n:str} AND a.is_active_from IS NOT NULL AND a.is_active_from <= {today:str}', [
            'i18n' => $this->session->i18n, 
            'm_type' => 'Models\Articles',
            'today' => date("Y-m-d H:i:s")
        ])
        ->orderBy('a.is_accent DESC, a.date DESC, a.id DESC');

    $paginator = new \Phalcon\Paginator\Adapter\QueryBuilder([
        "builder" => $builder,
        "limit" => 33,
        "page" => $this->request->getQuery('page', 'int', 1)
    ]);

    // Cache
    $cache_key = 'articles-index-'.$this->request->getQuery('page', 'int', 1);
    if ($this->di->has('dataCache') and $this->dataCache->exists($cache_key)) {
        $this->view->page = $this->dataCache->get($cache_key);
    } else {
        $this->view->page = $paginator->getPaginate();

        if ($this->di->has('dataCache')) {
            $this->dataCache->save($cache_key, $this->view->page);
        }
    }

So, the important part is the last few lines after "// Cache" comment :) I didnt cache the query, i cache all paginator data. For optimization it is good idea to specify which columns we need (in my situation i have a column content with huge texts, so i didnt need it). One more thing- do not forget to flush cache (from model from example) when you change/delete some element... something like this (in Model):

public function afterDelete()
{
    // Clear articles cache (but first check if we have that service)
    if ($this->getDI()->has('dataCache')) {
        $this->getDI()->get('dataCache')->flush();
    }
}

And that it is. From 127ms request, now ive got 40ms and less :). Good luck all :)

Some useful links: Phalcon Models Cache, Improving Performance in Phalcon, Phalcon File Cache

score 0 · Answer 2 · answered Sep 18 '16 at 17:35

0

Since getQuery() calls getPhql() internally anyways, the simplest solution I see is just call
->getPhql() on the query builder, then from there toss the string to createQuery like so:

$query = $this->modelsManager->createQuery($phql);

From there call $query->cache(...) followed by execute() as normal.
getPhql() isn't as hacky as it may seem as they propose its usage in the caching section of the manual.

answered Sep 18 '16 at 17:35

Ultimater

4,647
2
29
43

Can u explain more? I mean can u give me example with Paginator? – Boris Delev Sep 18 '16 at 17:50
Actually, on second thought, my approach is just a longer way to do `->getQuery()->cache(...)` because `getQuery() -> `returns [`Phalcon\Mvc\Model\QueryInterface`](https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/queryinterface.zep) which has a [cache](https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/query.zep) method. – Ultimater Sep 18 '16 at 18:33
In terms of paginating, though, you could send the cached data to [Phalcon\Paginator\Adapter\Model](https://docs.phalconphp.com/en/latest/api/Phalcon_Paginator_Adapter_Model.html). You can also cache the [resultset](https://docs.phalconphp.com/en/latest/reference/models-cache.html). Don't have the time now, but will try to add an example tomorrow. The Phalcon forums have a few examples. – Ultimater Sep 18 '16 at 18:37
But where i should use getQuery? ... i will wait for example with paginator. However, didnt find nothing in phalcon forum :/ – Boris Delev Sep 18 '16 at 18:42

How to Cache with QueryBuilder and Paginator in Phalcon?

2 Answers2