Allow to extend Query Builder to add raw cache

[eusonlito]

I'm working on a package to extend the Eloquent/Query Builder to add (for example) a cache to database results before any transformation.

I have currently this package https://github.com/eusonlito/Query-Remember that adds a macro on Elquent and Query builder to cache get, first, etc... results.

But models serialize and restore from cache have a lot problems, not always works (relations, casting, etc...) and have performance issues.

My idea is to cache and restore database results just after runSelect method

framework/src/Illuminate/Database/Query/Builder.php

Lines 2411 to 2416 in 4bffc87

	protected function runSelect()
	{
	return $this->connection->select(
	$this->toSql(), $this->getBindings(), ! $this->useWritePdo
	);
	}

The database raw results are very easy to cache cache an restore.

/**
 * Run the query as a "select" statement against the connection.
 *
 * @return array
 */
protected function runSelect()
{
    return $this->cache()->remember(
        $this->key(),
        $this->ttl(),
        fn () => $this->connection->select($this->toSql(), $this->getBindings(), ! $this->useWritePdo)
    );
}

I don't want:

• Bind Query Builder with other class.
• Extend Query Builder

The easy way maybe is adding a new Class extending Query Builder (also I need to change all Eloquents models to use this new class) with only this method (and cache related methods), but require too much changes on dependent models/code/configurations.

This can be an example extending (bad) the Query Builder.

class BuilderCache extends Builder
{
    /**
     * Adding method just before database query
     *
     * @return array
     */
    protected function runSelect()
    {
        return $this->cache()->remember(
            $this->cacheKey(),
            $this->cacheTtl(),
            fn () => $this->runSelectConnection()
        );
    }

    /**
     * Run the query as a "select" statement against the connection.
     *
     * @return array
     */
    protected function runSelectConnection()
    {
        return $this->connection->select($this->toSql(), $this->getBindings(), ! $this->useWritePdo);
    }

    /**
     * @return \Illuminate\Contracts\Cache\Repository
     */
    protected function cache(): Repository
    {
        if ($this->isTaggable() === false) {
            return $this->cache;
        }

        return $this->cache->tags($this->tags());
    }

    /**
     * @return string
     */
    protected function cacheKey(): string
    {
        return $this->cacheKey ?? $this->cacheKeyDefault();
    }

    /**
     * @return string
     */
    protected function cacheKeyDefault(): string
    {
        return $this->config['prefix'].md5($this->toSql().'|'.serialize($this->getBindings()));
    }

    /**
     * @return int
     */
    protected function cacheTtl(): int
    {
        return $this->cacheTtl ?? $this->config['ttl'];
    }

    /**
     * @return bool
     */
    protected function isTaggable(): bool
    {
        return $this->cacheRepository()->getStore() instanceof TaggableStore;
    }

    /**
     * @return array
     */
    protected function tags(): array
    {
        return $this->config['tags'];
    }
}

Any idea @taylorotwell :)

Thanks!

[eusonlito]

My solution shouldn't be a change all like https://laracasts.com/discuss/channels/guides/never-execute-a-duplicate-query-again

[eusonlito]

First attempt, I think that I can't do it without extend Illuminate\Database\Query\Builder:

<?php declare(strict_types=1);

namespace App\Domains\Shared\Database\Builder;

use DateInterval;
use DateTimeInterface;
use Illuminate\Contracts\Cache\Repository;
use Illuminate\Database\Query\Builder;

class Cache extends Builder
{

    /**
     * @var array
     */
    protected array $cacheConfig;

    /**
     * @var int|\DateTimeInterface|\DateInterval|null
     */
    protected int|DateTimeInterface|DateInterval|null $cacheTTL;

    /**
     * @var ?string
     */
    protected ?string $cacheKey;

    /**
     * @return array
     */
    protected function runSelect()
    {
        return $this->cacheResult(fn () => parent::runSelect());
    }

    /**
     * @param int|\DateTimeInterface|\DateInterval|null $ttl = null
     * @param ?string $key = null
     *
     * @return self
     */
    public function cache(int|DateTimeInterface|DateInterval|null $ttl = null, ?string $key = null): self
    {
        $this->cacheTTL = $ttl;
        $this->cacheKey = $key;

        return $this;
    }

    /**
     * @return self
     */
    protected function cacheReset(): self
    {
        $this->cacheTTL = null;
        $this->cacheKey = null;

        return $this;
    }

    /**
     * @param callable $result
     *
     * @return array
     */
    protected function cacheResult(callable $result): array
    {
        $response = fn () => tap($result(), fn () => $this->cacheReset());

        if ($this->cacheEnabled() === false) {
            return $response();
        }

        return $this->cacheManager()->remember($this->cacheKey(), $this->cacheTTL(), $response);
    }

    /**
     * @return bool
     */
    protected function cacheEnabled(): bool
    {
        return $this->cacheConfig('enabled') && $this->cacheTTL();
    }

    /**
     * @param ?string $key = null
     * @param string|int|bool|null $value = null
     *
     * @return mixed
     */
    protected function cacheConfig(?string $key = null, string|int|bool|null $value = null): mixed
    {
        $this->cacheConfig ??= $this->cacheConfigDefault();

        if ($value !== null) {
            return $this->cacheConfig[$key] = $value;
        }

        if ($key === null) {
            return $this->cacheConfig;
        }

        return $this->cacheConfig[$key] ?? null;
    }

    /**
     * @return array
     */
    protected function cacheConfigDefault(): array
    {
        static $config;

        return $config ??= config('database.cache', []) + [
            'enabled' => false,
            'driver' => 'redis',
            'ttl' => 3600,
            'tag' => 'database',
            'prefix' => 'database|',
        ];
    }

    /**
     * @return string
     */
    protected function cacheKey(): string
    {
        return $this->cacheKey ?? $this->cacheConfig('prefix').md5($this->toSql().'|'.serialize($this->getBindings()));
    }

    /**
     * @return int|\DateTimeInterface|\DateInterval|null
     */
    protected function cacheTTL(): int|DateTimeInterface|DateInterval|null
    {
        return $this->cacheTTL ?? $this->cacheConfig('ttl');
    }

    /**
     * @return \Illuminate\Contracts\Cache\Repository
     */
    protected function cacheManager(): Repository
    {
        $repository = $this->cacheRepository();

        if ($repository->supportsTags() && $this->cacheTagGlobal()) {
            $repository = $repository->tags($this->cacheTags());
        }

        return $repository;
    }

    /**
     * @return \Illuminate\Contracts\Cache\Repository
     */
    protected function cacheRepository(): Repository
    {
        static $repository;

        return $repository ??= resolve('cache')->store($this->cacheConfig('driver'));
    }

    /**
     * @return array
     */
    protected function cacheTags(): array
    {
        return array_filter([$this->cacheTagGlobal(), $this->cacheTagPrefix()]);
    }

    /**
     * @return ?string
     */
    protected function cacheTagGlobal(): ?string
    {
        return $this->cacheConfig('tag');
    }

    /**
     * @return ?string
     */
    protected function cacheTagPrefix(): ?string
    {
        if ($this->from) {
            return $this->cacheTagGlobal().'|'.$this->from;
        }

        return null;
    }
}

<?php declare(strict_types=1);

namespace App\Domains\Shared\Model\Traits;

use App\Domains\Shared\Database\Builder\Cache as CacheBuilder;

trait QueryBuilderCache
{
    /**
     * @return \Illuminate\Database\Query\Builder
     */
    protected function newBaseQueryBuilder()
    {
        return new CacheBuilder(($connection = $this->getConnection()), $connection->getQueryGrammar(), $connection->getPostProcessor());
    }
}

[eusonlito]

And published as package: https://github.com/eusonlito/laravel-database-cache