Laravel Eloquent Caching pagination aggregate count result

irlmq6kh  于 2023-10-22  发布在  其他
关注(0)|答案(1)|浏览(102)

是否可以缓存分页查询计数结果?

select count(*) as aggregate from table_name

我有一个表,有2 000 000+,每次计算这个计数都需要300毫秒。缓存此结果为1小时将是足够的解决方案,谢谢!

ymzxtsji

ymzxtsji1#

我需要扩展Illuminate\Database\Eloquent\ModelIlluminate\Database\Eloquent\Builder

我的扩展Builder类

<?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,
        ]);
    }

}

我的扩展Model类

<?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);
    }
}

现在我只需要用这个新的CachedModel类扩展我的任何模型,以应用总计数结果的缓存。

相关问题