Laravel Eloquent Caching pagination aggregate count result

Question

is somehow possible to cache count result for pagination query?

select count(*) as aggregate from table_name

I have table with 2 000 000+ and it tooks ~300ms every time to calculate this count. Cache this result for 1 hour would be sufficient solution, thank you!

Answer 1

I needed to extend Illuminate\Database\Eloquent\Model and Illuminate\Database\Eloquent\Builder.

My extended Builder class

<?php

namespace App\Laravel\Database\Eloquent;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Pagination\Paginator;
use Illuminate\Support\Facades\Cache;

/**
 * @mixin \Illuminate\Database\Query\Builder
 */
class CachedBuilder extends Builder
{
    const CACHE_THRESHOLD = 10000;
    const CACHE_DURATION = 60;
    const CACHE_KEY_PREFIX = 'pagination_';

    /**
     * Paginate the given query.
     *
     * @param  int  $perPage
     * @param  array  $columns
     * @param  string  $pageName
     * @param  int|null  $page
     * @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
     *
     * @throws \InvalidArgumentException
     */
    public function paginate($perPage = null, $columns = ['*'], $pageName = 'page', $page = null)
    {
        $page = $page ?: Paginator::resolveCurrentPage($pageName);

        $perPage = $perPage ?: $this->model->getPerPage();

        $key = self::CACHE_KEY_PREFIX . '_' . $this->getModel()->getTable();
        $key .= '_'.md5($this->query->toSql().join('/', $this->query->getBindings()));

        $total = Cache::get($key);
        if(!$total) {
            $total = $this->toBase()->getCountForPagination();
            if($total > self::CACHE_THRESHOLD) {
                Cache::put($key, $total, self::CACHE_DURATION);
            }
        }

        $results = $total ? $this->forPage($page, $perPage)->get($columns)
                    : $this->model->newCollection();

        return $this->paginator($results, $total, $perPage, $page, [
            'path' => Paginator::resolveCurrentPath(),
            'pageName' => $pageName,
        ]);
    }

}

My extended Model class

<?php

namespace App\Laravel\Database\Eloquent;

use Illuminate\Database\Eloquent\Model;

abstract class CachedModel extends Model
{

    /**
     * @param \Illuminate\Database\Query\Builder $query
     * @return CachedBuilder|\Illuminate\Database\Eloquent\Builder|static
     */
    public function newEloquentBuilder($query)
    {
        return new CachedBuilder($query);
    }
}

And now i just need to extend any of my models with this new CachedModel class to apply caching of total count results.