Framework: Add ability to cache database queries during a session.

Created on 26 Jul 2016 · 5Comments · Source: laravel/framework

By 'session' I mean the time during which a request is made.

I am building a complicated, multi-tenet CMS for the business that I work for. During the development, I have noticed that many duplicate queries are made to the database. Despite whether this is a design/programming error on my part, I think it would be great if queries made to the database were cached into memory for the duration of the script runtime. If a duplicate query were to be made to the same connection, instead of querying the database, it would just return the previous results. This could be overridden by a possible ignoreCache() method.

Example:

//First query:
DB::table('users')->select('*')->get();

//Second duplicate query elsewhere in the code (shouldn't ever reach the database):
User::all();

//Another duplicate query for which we want to ignore the cache (queries the database):
User::create(...);
User::ignoreCache()->get();

The purpose of this is to silently optimize the performance of the application, reducing the number of calls to the database. The "cache" would possibly be stored in the service container, or another in-memory implementation. There would be no need to save the queries to the "real" cache (memcached, redis, file, etc) because it wouldn't live beyond the current request.

I began researching the possibility of implementing this myself for a PR, but there are numerous places where a call to the database is made in between the the Eloquent Builder and the Query Builder. Due to the amount of time needed and the possibility of it being rejected, I wanted to discuss it before putting the necessary time in. I would be willing to work on it if I knew this was something that would be accepted.

Source

pstephan1187

👍2

Most helpful comment

Have a look at my library: https://github.com/spiritix/lada-cache

spiritix on 28 Jul 2017

👍3

All 5 comments

I've needed to do the same thing in the past, but there really isn't a solid way for the framework itself to handle that caching for you automatically, because it's so application-specific.

Here's what I've done:

Example Usage:

To use the cache, you wrap your query in a closure. The cache (which you'll see below) uses debug_backtrace to get the argument passed to getTeam, and then in turn passes that argument to the closure. So the cache closure signature is ALWAYS the same as the actual method signature. This means the cache also tracks different arguments provided to the same method, separately.

class Player extends Model implements PlayerInterface
{
    use ModelCache;

    /**
     * {@inheritdoc}
     */
    public function getTeam(SectionInterface $section)
    {
        return $this->cache(function(SectionInterface $section) {
            return $this->teams()
                        ->where('section_id', $section->getID())
                        ->first();
        });
    }

The ModelCache trait:

You can see that $key is a combination of the called class, the called method, and an md5 hash of the serialized arguments, which makes each function call with different arguments, a unique cache entry.

trait ModelCache
{
    /**
     * Gets the cached result by key, or executes the closure if cache key does not exist
     * @param \Closure $closure
     * @return mixed
     */
    protected function cache(\Closure $closure)
    {
        $backtrace = debug_backtrace(DEBUG_BACKTRACE_PROVIDE_OBJECT,2)[1];

        $class = $backtrace['class'];

        $method = $backtrace['function'];

        $args = $backtrace['args'];

        $key = $class . '::'. $method . '.' . md5(serialize($args));

        if (!array_key_exists($key, ModelCacheStore::$cache)) {
            ModelCacheStore::$cache[$key] = call_user_func_array($closure, $args);
        }

        return ModelCacheStore::$cache[$key];
    }
}

The ModelCacheStore singleton.

Cache storage is pretty straight-forward: a static singleton that keeps the cache for the current request. No actual caching through Redis/Memcache involved (as you had mentioned).

class ModelCacheStore
{
    public static $cache = [];

    public static function clear()
    {
        self::$cache = [];
    }
}

The ModelSerivceProvider for cache invalidation

All this does is listen for eloquent events, and aggressively clobbers the _entire_ cache when any model changes. This means the cache isn't perfect, but it's a _hell_ of a lot safer than trying to surgically invalidate just one model cache. The reason being is a model could belong to another via a relationship, and if that loaded relation is cached, it could be different from the model you just updated in the DB. So the safe thing to do is just nuke the whole cache on save/update/create.

class ModelServiceProvider extends ServiceProvider
{
    public function boot()
    {
        Event::listen(['eloquent.saved: *', 'eloquent.created: *', 'eloquent.updated: *'], function() {
            ModelCacheStore::clear();
        });
    }
}

I don't have a way of forcefully getting the raw query since my application doesn't need such a feature, but I'm sure it would be easy enough to add it.

It would be nice if Laravel offered a similar feature out of the box though, as my app often repeats the same query in different contexts throughout the request lifecycle, and "request-only caching" is a must for the performance of my app as well.

jlem on 26 Jul 2016

❤3

Thank you @jlem for your code and response. I already have something similar in place, however, it doesn't cover all duplicate queries. Many times you will have the same queries being made across classes or methods or even between Eloquent and the Query Builder. I was wanting a way to cache based on the very query being made. That way, no matter where the query was being made from, if that SQL statement was called already, it wouldn't be called again unless forced to.

pstephan1187 on 26 Jul 2016

👍2

For suggestions and feature requests please use the https://github.com/laravel/internals repo.

Closing this issue then :)