INSERT IGNORE using Laravel's Fluent

Question

Is there a quick way to modify a SQL query generated by Laravel's Fluent to have an INSERT IGNORE instead of the usual INSERT?

I'm trying to insert an array with fifty elements. Writing out the entire query manually will bloat up the code and make it more susceptible to human errors.

Answer 1

Answer for Laravel 5.8.33+

If anyone reads this nowadays: there's no need for any hacks or Query Builder extensions. The query builder natively provides an insertOrIgnore method that does just that.

Just use

DB::table('tablename')->insertOrIgnore([
    ['column_name' => 'row1', 'column2_name' => 'row1'],
    ['column_name' => 'row2', 'column2_name' => 'row2']
]);

See the documentation or the API docs for details.

Answer 2

Try this magic, in your model:

public static function insertIgnore($array){
    $a = new static();
    if($a->timestamps){
        $now = \Carbon\Carbon::now();
        $array['created_at'] = $now;
        $array['updated_at'] = $now;
    }
    DB::insert('INSERT IGNORE INTO '.$a->table.' ('.implode(',',array_keys($array)).
        ') values (?'.str_repeat(',?',count($array) - 1).')',array_values($array));
}

Use like this:

Shop::insertIgnore(array('name' => 'myshop'));

This is a great way to prevent constraint violations that may occur with firstOrCreate in a multi-user environment, if that 'name' property was a unique key.

Answer 3

I couldn't monkey patch as suggested in Rastislav's answer.

This is what worked for me:

1. Override compileInsert method in a custom Query Grammar class, which extends the framework's MySqlGrammar class.

2. Use an instance of this custom grammar class by calling the setQueryGrammar method from the DB connection instance.

So, the class code is like this:

<?php

namespace My\Namespace;

use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Grammars\MySqlGrammar;

/**
 * Changes "INSERT" to "INSERT IGNORE"
 */
class CustomMySqlGrammar extends MySqlGrammar
{
    /**
     * Compile an insert statement into SQL.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @param  array  $values
     * @return string
     */
    public function compileInsert(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 = collect($values)->map(function ($record) {
            return '('.$this->parameterize($record).')';
        })->implode(', ');

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

I copied the compileInsert method from the framework's class and then, inside the method, I have only changed insert to insert ignore. Everything else has been kept the same.

Then, in the specific spot of code, in the application (a scheduled task), where I needed "insert ignore", I have simply done as follows:

<?php

use DB;
use My\Namespace\CustomMySqlGrammar;

class SomeClass
{
    public function someMethod()
    {
        // Changes "INSERT" to "INSERT IGNORE"
        DB::connection()->setQueryGrammar(new CustomMySqlGrammar());

        // et cetera... for example:
        ModelClass::insert($data);
    }
}