Cache the query

[22syamsudar]

how can we cache the query? like caching the sql query in laravel? thank you...

[rappasoft]

It might be hard to because it would change based on what you click?

[22syamsudar]

yeah.. i think so.. because it has form filter...
but i need to make cache for the sql query to make more faster...

[inmanturbo]

I would recommend implementing your own custom caching builder, which could cache queries until they change, something like the following:

<?php

namespace App\Query;

use Cache;
use Illuminate\Database\Query\Builder as QueryBuilder;

class CachingBuilder extends QueryBuilder
{
    /**
     * Run the query as a "select" statement against the connection.
     *
     * @return array
     */
    protected function runSelect()
    {

        // config option here is just for example
        // in case you ever might want to turn it off.
        // By default database config file contains no such key --
        // you would have to add it.
        if (config('database.cache_queries')) {
            return Cache::store('request')->remember($this->getCacheKey(), 1, function () {
                return parent::runSelect();
            });
        }

        return parent::runSelect();
    }

    /**
     * Returns a Unique String that can identify this Query.
     *
     * @return string
     */
    protected function getCacheKey()
    {
        return json_encode([
            $this->toSql() => $this->getBindings(),
        ]);
    }
}

And add the request store to config/cache.php:

   /*
    |--------------------------------------------------------------------------
    | Cache Stores
    |--------------------------------------------------------------------------
    |
    | Here you may define all of the cache "stores" for your application as
    | well as their drivers. You may even define multiple stores for the
    | same cache driver to group types of items stored in your caches.
    |
    | Supported drivers: "apc", "array", "database", "file",
    |         "memcached", "redis", "dynamodb", "octane", "null"
    |
    */

    'stores' => [

...
        'request' => [
            'driver' => 'array', // can use any driver here you like, array will cache just for single request to avoid duplicate queries, at least
        ],

        'array' => [
            'driver' => 'array',
            'serialize' => false,
        ],

        'database' => [
            'driver' => 'database',
            'table' => 'cache',
            'connection' => null,
            'lock_connection' => null,
        ],

...
    ],

And then in any model you want to cache queries you could add this method:

    /**
     * Get a new query builder instance for the connection.
     *
     * @return \App\Query\CachingBuilder
     */
    protected function newBaseQueryBuilder()
    {
        $conn = $this->getConnection();

        $grammar = $conn->getQueryGrammar();

        return new \App\Query\CachingBuilder($conn, $grammar, $conn->getPostProcessor());
    }

or create a trait:

<?php
namespace App\Concerns;  // or App\Traits, whichever you prefer

use App\Query\CachingBuilder;

trait CachesQueries
{
    /**
     * Get a new query builder instance for the connection.
     *
     * @return \App\Query\CachingBuilder
     */
    protected function newBaseQueryBuilder()
    {
        $conn = $this->getConnection();

        $grammar = $conn->getQueryGrammar();

        return new CachingBuilder($conn, $grammar, $conn->getPostProcessor());
    }
}

Use it :

<?php

namespace App\Models;

use App\Concerns\CachesQueries;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use HasFactory, Notifiable, CachesQueries;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name',
        'email',
        'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];
}