Is there a way to use query builder result cache?

[fd6130]

Hi, i'm using fractal + pagerfanta for pagination my result in my project. I wonder how can i apply result cache in my query builder when using pagerfanta?

        $adapter = new QueryAdapter($result);
        
        $pagerfanta = new Pagerfanta($adapter);

        $pagerfanta->setMaxPerPage($limitPerPage);
        $pagerfanta->setCurrentPage($page);

        $paginatorAdapter = new PagerfantaPaginatorAdapter($pagerfanta, function(int $page) use ($request, $limitPerPage) {
            $route = $request->attributes->get('_route');
            $inputParams = $request->attributes->get('_route_params');
            $newParams = array_merge($inputParams, $request->query->all());
            $newParams['page'] = $page;
            $newParams['limit'] = $limitPerPage;
            return $this->router->generate($route, $newParams, UrlGeneratorInterface::ABSOLUTE_URL);
        });

        $resource = new Collection($pagerfanta->getCurrentPageResults(), $transformer, 'data');
        $resource->setPaginator($paginatorAdapter);

[mbabker]

In one of my projects, we use the result cache with the Doctrine ORM query adapter in a few different circumstances, for those you'd just need to call $qb->enableResultCache() as you would elsewhere before getting the current page's results. Do make sure your cache is pagination aware (and if your request supports them, filters as well) that way you aren't serving the wrong page's results because of an invalid cache.

There isn't a way to use the result cache with the Doctrine DBAL query adapter, the query builder's execute() method doesn't give a way to call the connection's executeQuery() method with a query cache profile. So if you wanted to use a result cache with the DBAL, you'd need to make your own adapter and give it a way to set up a cache profile.

[fd6130]

So i need to call enableResultCache() before passing it to QueryAdapter?

$qb->enableResultCache();

$adapter = new QueryAdapter($qb);

[mbabker]

Correct.

[fd6130]

But it have to be after $qb->getQuery() to use enableResultCache(). What will happen if i pass to new QueryAdapter($qb) after i execute the $qb->getQuery()->enableResultCache() before that? Will it produce incorrect paginate result?

[mbabker]

It’s going to depend on how you configure the cache. You’ll need a dynamic cache key to make sure you’re getting the right results for each page in your list (so something like sprintf('my_list_page_%d', $page) at a minimum), then the rest of it relies on the internals of Doctrine\ORM\Tools\Pagination\Paginator which makes sure that the queries to count the list are uncached while applying your cache config when fetching the current page’s list of items.