Laravel Techniques: Extending the Query Builder

In this article I’m going to share a method for extending the query builder functionality. For example, let’s say we have a caching function that we didn’t want to have to type (or copy/paste) every time we need it when loading results from our database.

$model = Cache::remember('query:my_awesome_cache', 15, function () {
    return DB::query()->select('*')->from('my_model')->get();
});

It could get tedious to wrap your query in this function each time you need it. It would be so much easier to do something like this instead:

$model = DB::query()->withCache()->select('*')
    ->from('my_model')->get();

Unfortunately, Laravel doesn’t have this built into the query builder, so we’ll have to add it ourselves by extending our default database connection. Fortunately, Laravel gives us a way to do this in the DatabaseManager class:

public function extend($name, callable $resolver)
{
    $this->extensions[$name] = $resolver;
}

We can use this to our advantage to load a custom connection resolver that could instantiate a custom query builder class containing our withCache method.

Overriding the QueryBuilder and Connection Classes

To get started, we’ll need to define our custom classes first. Let’s start with creating a custom query builder class, where we will place our withCache method.

For this class, we can simply place the Cache::remember call in the get method and check to see if a boolean was set. For the cache name, we can get the raw query string and hash it, that way we don’t have to worry about coming up with unique names.

namespace App\Override;
class QueryBuilder extends \Illuminate\Database\Query\Builder {
    private $withCache = 0;
    public function withCache() {
            $this->withCache = 1;
            return $this;
    }
    //@Override
    public function get($columns = ['*']) {
        //If withCache() was called, let's cache the query
        if ($this->withCache) {
            //Get the raw query string with the PDO bindings
            $sql_str = str_replace('?', '"%s"', $this->toSql());
            $sql_str = vsprintf($sql_str, $this->getBindings());
            return Cache::remember(
                'query:' . hash('sha256', $sql_str),
                15,
                function () use ($columns) {
                    return parent::get($columns);
                }
            );
        } else {
            //Return default
            return parent::get($columns);
        }
    }
}

Now we can create our Connection class that will load our query builder when called.

namespace App\Override;
class Connection extends \Illuminate\Database\MySqlConnection {
    //@Override
    public function query() {
        return new QueryBuilder(
            $this,
            $this->getQueryGrammar(),
            $this->getPostProcessor()
        );
    }
}

Creating a Custom Provider

Now that our custom classes are set up, we can continue with extending the connection by creating our own service provider and placing it in our app/Providers folder. We can just copy/paste the code from Illuminate/Database/DatabaseServiceProvider.php, but we’ll need to modify the registerConnectionServices() function where it registers the db singleton.

use App\Override\Connection;
class CustomDatabaseServiceProvider extends ServiceProvider {
    ...
    protected function registerConnectionServices() {
        ...
        $this->app->singleton('db', function ($app) {
            //Load the default DatabaseManager
            $dbm = new DatabaseManager($app, $app['db.factory']);

From here we can call the extend() function on $dbm to load our own connection class. However, we’ll need to create the default connection first with the db.factory singleton in order to get the connection data. This can be achieved passing in the $app object to our callback.

//Extend to include the custom connection (MySql in this example)
$dbm->extend('mysql', function($config, $name) use ($app) {
    //Create default connection from factory
    $connection = $app['db.factory']->make($config, $name);
    //Instantiate our connection with the default connection data
    $new_connection = new Connection(
        $connection->getPdo(),
        $connection->getDatabaseName(),
        $connection->getTablePrefix(),
        $config
    );
    //Set the appropriate grammar object
    $new_connection->setQueryGrammar(new QueryGrammar());
    $new_connection->setSchemaGrammar(new SchemaGrammar());
    return $new_connection;
});
return $dbm;

After that, we’ll just need to update the config/app.php file to remove the default DatabaseServiceProvider and point to ours instead.

'providers' => [
    ...
    //Illuminate\Database\DatabaseServiceProvider::class,
    App\Providers\CustomDatabaseServiceProvider::class,
    ...