[Proposal] Query Cache during script execution

[Surt]

The idea is to mimic the Idiorm cache system. Every call to "get" on the query builder will check if the query was called before, and take the results from memory instead database.

It's really really easy to implement. this works now on Illuminate\Database\Query\Builder, coping the idiorm way just on the "get" method. I just check and return the cache on the get method. So, every "get" call is cached during script execution. And if you make multiple calls to the same query it is executed just 1 time. It works for every method wich uses the querybuilder get, so models etc are affected by this performance improvement.

Of course it needs some tweaking, maybe you prefer to add it with a config option, or inject it someway, etc.

        /**
         * Create a cache key for the given query and parameters.
         */
        protected static function _create_cache_key($query, $parameters) {
            $parameter_string = join(',', $parameters);
            $key = $query . ':' . $parameter_string;
            return sha1($key);
        }

        /**
         * Check the query cache for the given cache key. If a value
         * is cached for the key, return the value. Otherwise, return false.
         */
        protected static function _check_query_cache($cache_key, $connection_name = 'default') {
            if (isset(self::$_query_cache[$connection_name][$cache_key])) {
                return self::$_query_cache[$connection_name][$cache_key];
            }
            return false;
        }

        /**
         * Clear the query cache
         */
        public static function clear_cache() {
            self::$_query_cache = array();
        }

        /**
         * Add the given value to the query cache.
         */
        protected static function _cache_query_result($cache_key, $value, $connection_name = 'default') {
            if (!isset(self::$_query_cache[$connection_name])) {
                self::$_query_cache[$connection_name] = array();
            }
            self::$_query_cache[$connection_name][$cache_key] = $value;
        }

    /**
     * Execute the query as a "select" statement.
     *
     * @param  array  $columns
     * @return array
     */
    public function get($columns = array('*'))
    {
        // If no columns have been specified for the select statement, we will set them
        // here to either the passed columns, or the standard default of retrieving
        // all of the columns on the table using the "wildcard" column character.
        if (is_null($this->columns))
        {
            $this->columns = $columns;
        }

        $query = $this->toSql();

        $cache_key = self::_create_cache_key($query, $this->bindings);

        if(!$results = self::_check_query_cache($cache_key)){
            $results = $this->connection->select($query, $this->bindings);
            $this->processor->processSelect($this, $results);
            self::_cache_query_result($cache_key, $results);
        }

        return $results;
    }

I setted the $connection_name for the cache as a fake one, don't know laravel db too well to set the actual connection name.

I make a mistake and wrote it at the laravel git sorry https://github.com/laravel/laravel/issues/519#issuecomment-18268444

[Surt]

I'm using the idiorm functions, as a simple cache system. Don't want to use the laravel cache with memory with the aim of not adding more dependencies to the database library.

[taylorotwell]

This type of thing causes memory problems on larger applications.