Laravel support for replace into / insert ignore / insert on duplicate key update

Builder.php

<?php

namespace App\Library\Database\Query;

use Illuminate\Database\Query\Builder as QueryBuilder;

class Builder extends QueryBuilder
{
    /**
     * Insert a new record into the database.
     *
     * @param  array  $values
     * @return bool
     */
    public function replace(array $values)
    {
        if (empty($values)) {
            return true;
        }

        // Since every insert gets treated like a batch insert, we will make sure the
        // bindings are structured in a way that is convenient for building these
        // inserts statements by verifying the elements are actually an array.
        if (! is_array(reset($values))) {
            $values = [$values];
        }

        // Since every insert gets treated like a batch insert, we will make sure the
        // bindings are structured in a way that is convenient for building these
        // inserts statements by verifying the elements are actually an array.
        else {
            foreach ($values as $key => $value) {
                ksort($value);
                $values[$key] = $value;
            }
        }

        // We'll treat every insert like a batch insert so we can easily insert each
        // of the records into the database consistently. This will make it much
        // easier on the grammars to just handle one type of record insertion.
        $bindings = [];

        foreach ($values as $record) {
            foreach ($record as $value) {
                $bindings[] = $value;
            }
        }

        $sql = $this->grammar->compileReplace($this, $values);

        // Once we have compiled the insert statement's SQL we can execute it on the
        // connection and return a result as a boolean success indicator as that
        // is the same type of result returned by the raw connection instance.
        $bindings = $this->cleanBindings($bindings);

        return $this->connection->insert($sql, $bindings);
    }

    /**
     * Insert a new record into the database.
     *
     * @param  array  $values
     * @return bool
     */
    public function insertUpdate(array $values)
    {
        if (empty($values)) {
            return true;
        }

        // Since every insert gets treated like a batch insert, we will make sure the
        // bindings are structured in a way that is convenient for building these
        // inserts statements by verifying the elements are actually an array.
        if (! is_array(reset($values))) {
            $values = [$values];
        }

        // Since every insert gets treated like a batch insert, we will make sure the
        // bindings are structured in a way that is convenient for building these
        // inserts statements by verifying the elements are actually an array.
        else {
            foreach ($values as $key => $value) {
                ksort($value);
                $values[$key] = $value;
            }
        }

        // We'll treat every insert like a batch insert so we can easily insert each
        // of the records into the database consistently. This will make it much
        // easier on the grammars to just handle one type of record insertion.
        $bindings = [];

        foreach ($values as $record) {
            foreach ($record as $value) {
                $bindings[] = $value;
            }
        }

        $sql = $this->grammar->compileInsertUpdate($this, $values);
        
        // Once we have compiled the insert statement's SQL we can execute it on the
        // connection and return a result as a boolean success indicator as that
        // is the same type of result returned by the raw connection instance.
        $bindings = $this->cleanBindings($bindings);

        return $this->connection->insert($sql, array_merge($bindings, $bindings));
    }

    /**
     * Insert a new record into the database.
     *
     * @param  array  $values
     * @return bool
     */
    public function insertIgnore(array $values)
    {
        if (empty($values)) {
            return true;
        }

        // Since every insert gets treated like a batch insert, we will make sure the
        // bindings are structured in a way that is convenient for building these
        // inserts statements by verifying the elements are actually an array.
        if (! is_array(reset($values))) {
            $values = [$values];
        }

        // Since every insert gets treated like a batch insert, we will make sure the
        // bindings are structured in a way that is convenient for building these
        // inserts statements by verifying the elements are actually an array.
        else {
            foreach ($values as $key => $value) {
                ksort($value);
                $values[$key] = $value;
            }
        }

        // We'll treat every insert like a batch insert so we can easily insert each
        // of the records into the database consistently. This will make it much
        // easier on the grammars to just handle one type of record insertion.
        $bindings = [];

        foreach ($values as $record) {
            foreach ($record as $value) {
                $bindings[] = $value;
            }
        }

        $sql = $this->grammar->compileInsertIgnore($this, $values);
        
        // Once we have compiled the insert statement's SQL we can execute it on the
        // connection and return a result as a boolean success indicator as that
        // is the same type of result returned by the raw connection instance.
        $bindings = $this->cleanBindings($bindings);

        return $this->connection->insert($sql, $bindings);
    }
}

Connection.php

<?php

namespace App\Library\Database;

use Illuminate\Database\Connection as BaseConnection;
use App\Library\Database\Query\Builder;

class Connection extends BaseConnection
{
    /**
     * Get a new query builder instance.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    public function query()
    {
        return new Builder(
            $this, $this->getQueryGrammar(), $this->getPostProcessor()
        );
    }
}

example.php

<?php

DB::table('table')->replace([
  'dashboard_id' => $this->model->id,
  'date' => Carbon::now()->format('Y-m-d'),
  'views' => DB::raw('views + 1')
]);


DB::table('table')->insertIgnore([
  'dashboard_id' => $this->model->id,
  'date' => Carbon::now()->format('Y-m-d'),
  'views' => DB::raw('views + 1')
]);


DB::table('table')->insertUpdate([
  'dashboard_id' => $this->model->id,
  'date' => Carbon::now()->format('Y-m-d'),
  'views' => DB::raw('views + 1')
]);

MySqlConnection.php

<?php

namespace App\Library\Database;

use Illuminate\Database\Query\Processors\MySqlProcessor;
use App\Library\Database\Query\Grammars\MySqlGrammar as QueryGrammar;
use Illuminate\Database\MySqlConnection as Connection;

class MySqlConnection extends Connection
{
    /**
     * Get the default query grammar instance.
     *
     * @return \Illuminate\Database\Query\Grammars\MySqlGrammar
     */
    protected function getDefaultQueryGrammar()
    {
        return $this->withTablePrefix(new QueryGrammar);
    }

    /**
     * Get the default post processor instance.
     *
     * @return \Illuminate\Database\Query\Processors\MySqlProcessor
     */
    protected function getDefaultPostProcessor()
    {
        return new MySqlProcessor;
    }
}

MySqlGrammar.php

<?php

namespace App\Library\Database\Query\Grammars;

use Illuminate\Database\Query\Grammars\MySqlGrammar as Grammar;
use App\Library\Database\Query\Builder;

class MySqlGrammar extends Grammar
{
    /**
     * Compile an replace into statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileReplace(Builder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        if (! is_array(reset($values))) {
            $values = [$values];
        }

        $columns = $this->columnize(array_keys(reset($values)));

        // We need to build a list of parameter place-holders of values that are bound
        // to the query. Each insert should have the exact same amount of parameter
        // bindings so we will loop through the record and parameterize them all.
        $parameters = [];

        foreach ($values as $record) {
            $parameters[] = '('.$this->parameterize($record).')';
        }

        $parameters = implode(', ', $parameters);

        return "replace into $table ($columns) values $parameters";
    }

    /**
     * Compile an insert ignore statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileInsertIgnore(Builder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        if (! is_array(reset($values))) {
            $values = [$values];
        }

        $columns = $this->columnize(array_keys(reset($values)));

        // We need to build a list of parameter place-holders of values that are bound
        // to the query. Each insert should have the exact same amount of parameter
        // bindings so we will loop through the record and parameterize them all.
        $parameters = [];

        foreach ($values as $record) {
            $parameters[] = '('.$this->parameterize($record).')';
        }

        $parameters = implode(', ', $parameters);

        return "insert ignore into $table ($columns) values $parameters";
    }

    /**
     * Compile an insert ignore statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileInsertUpdate(Builder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        // Each one of the columns in the update statements needs to be wrapped in the
        // keyword identifiers, also a place-holder needs to be created for each of
        // the values in the list of bindings so we can make the sets statements.
        $columns = [];

        $values = reset($values);

        foreach ($values as $key => $value) {
            $columns[] = $this->wrap($key).' = '.$this->parameter($value);
        }
        $columns = implode(', ', $columns);

        return "insert into $table set $columns ON DUPLICATE KEY UPDATE $columns";
    }
}

Hey !

Seems really nice. I don't know if i'm dumb or i simply lack experience but i have no idea how to implement your code into laravel framework.
Could you help me ? I REALLY need this feature ! :)

Hi @plante-david55,

Assuming you know how to create the above files in the correct directory the missing part of the puzzle is to create a service provider for to override the default MySqlConnection.

Something like this:

<?php namespace App\Providers;

use App\Library\Database\MySqlConnection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{

    /**
     * Register the modified database connection
     *
     * @return void
     */
    public function register()
    {
        $this->app->singleton('db.connection.mysql', function ($app, $parameters) {
            // get the parameters
            list($connection, $database, $prefix, $config) = $parameters;

            // now we create an instance of our mysql connection
            return new MySqlConnection($connection, $database, $prefix, $config);
        });
    }
}

Then register the provider in config/app.php.

'providers' =>[
    ...
    App\Providers\DatabaseServiceProvider::class,
],

And thanks @VinceG!

This looks great! Perfect for bulk imports. I followed @ianchadwick's instructions, but I'm stuck at this error:

In Builder.php line 2526:

  Method Illuminate\Database\Query\Builder::replace does not exist.

I've taken a look at the following articles:

https://stidges.com/extending-the-connection-class-in-laravel
https://medium.com/@justin.park001/laravel-techniques-extending-the-query-builder-2f4aca7956a2

...but I just can't figure out a clean way to get it to use the new Builder class.

Could someone prod me in the right direction?

---

Spoke too soon, just got it working! I followed @SETIexplorer's advice in the comments of the first link.

First, the custom DatabaseServiceProvider must be loaded before Laravel's DatabaseServiceProvider:

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

Here's my DatabaseServiceProvider class:

<?php

namespace App\Providers;

use App\Library\Database\MySqlConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    /**
     * Override the default connection for MySQL. This allows us to use `replace` etc.
     *
     * @link https://stidges.com/extending-the-connection-class-in-laravel
     * @link https://gist.github.com/VinceG/0fb570925748ab35bc53f2a798cb517c
     * 
     * @return void
     */
    public function boot()
    {
        Connection::resolverFor('mysql', function($connection, $database, $prefix, $config) {
            return new MySqlConnection($connection, $database, $prefix, $config);
        });
    }
}

I then removed Connection, moved its query method to MySqlConnection, and slimmed it down a bit:

<?php

namespace App\Library\Database;

use App\Library\Database\Query\Builder;
use App\Library\Database\Query\Grammars\MySqlGrammar as QueryGrammar;
use Illuminate\Database\MySqlConnection as Connection;

class MySqlConnection extends Connection
{
    /**
     * Get the default query grammar instance.
     *
     * @return \App\Library\Database\Query\Grammars\MySqlGrammar
     */
    protected function getDefaultQueryGrammar()
    {
        return $this->withTablePrefix(new QueryGrammar);
    }

    /**
     * Get a new query builder instance.
     *
     * @return \App\Library\Database\Query\Builder
     */
    public function query()
    {
        return new Builder(
            $this, $this->getQueryGrammar(), $this->getPostProcessor()
        );
    }
}

Seems to be working great! I guess semantically, Builder should be renamed to MySqlBuilder, since that's what it is now.

Is there ever a situation where the new Builder methods would be used outside MySQL context? Do any other database engines implement the same methods? I'm not sure if my adjustments are the way to go in that case.

I don't know if this is the best solution, so please do let me know if there is a cleaner way.

The insertUpdate command here is malformed. I fixed it for our project. See commit for details:

art-institute-of-chicago/data-service-images@0802b89

Requires a couple minor changes in Builder::insertUpdate(), and a rewrite of MySqlGrammar::compileInsertUpdate().

Resources:

• https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
• https://gist.github.com/RuGa/5354e44883c7651fd15c

@IllyaMoskvin thx for your comment! I followed your way but it still use Illuminate\Database\Query\Builder anyway.

My laravel version is 5.2, and App\Library\Database\ is 100% same as your project [0].

I tried these way:

<?php

namespace App\Providers;

use App\Library\Database\MySqlConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    /**
     * Override the default connection for MySQL. This allows us to use `replace` etc.
     *
     * @link https://stidges.com/extending-the-connection-class-in-laravel
     * @link https://gist.github.com/VinceG/0fb570925748ab35bc53f2a798cb517c
     *
     * @return void
     */
    public function boot()
    {
        Connection::resolverFor('mysql', function ($connection, $database, $prefix, $config) {
            return new MySqlConnection($connection, $database, $prefix, $config);
        });
    }

     /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        // Or this way
        // $this->app->singleton('db.connection.mysql', function ($app, $parameters) {
        //     list($connection, $database, $prefix, $config) = $parameters;
        //     return new MySqlConnection($connection, $database, $prefix, $config);
        // });

        // Or this way
        // $this->app->bind('db.connection.mysql', \App\Library\Database\MySqlConnection::class);
    }
}

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

---

@IllyaMoskvin It is my mistake, i am using lumen 5.4 not laravel 5.2. I should register provider in bootstrap/app.php:

<?php
...
$app->register(App\Providers\DatabaseServiceProvider::class);
return $app;

Now it working!