Query Builder with Paginator and caching

You can better use the resultset adapter to cache the result instead of using the query builder adapter.

Ok, forget the Paginator. The QueryBuilder has a method called "cache". But it doesn't seem to be working.

$builder->getQuery()->cache(array('key' => 'key', 'lifetime' => 120));

I have set up the modelsCache in the bootstrap, it is working fine for phql queries, but not for query builder generated ones. Obviously I'm doing something wrong, but what?

This works.

      ->getQuery()->cache(array('key' => 'key', 'lifetime' => 120))->execute();

So... can u show completed example with the cache inside?

Well... after a weeks of thinking xD ... i find some good answer for my issue. I ask same question in StackOverflow. So...

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:

         $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