INSERT IGNORE using Laravel Fluent

Question

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

I am trying to insert an array of fifty elements. Writing the entire request manually will lead to code decomposition and make it more susceptible to human errors.

Answer 1

For the task, you need to create a new grammar that will contain the desired line:

grammar.php ⁽¹⁾

Grammar is a public property of the DB repository, or in this case Database . This is not very straightforward, but due to the visibility of the properties you will need to insert your special grammar into the database level.

I also suggest that you bring a problem with the project, maybe they have a better idea how to make it more flexible for such cases.

---

⁽¹⁾ This was the first, prior to the date of response to the response. If you see this today, you need to upgrade to the version of Laravel that you are using, for example. Grammar.php for 4.0 , these classes have moved to laravel/framework .

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 this:

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

This is a great way to prevent restrictions that may occur with firstOrCreate in a multi-tenant environment if this name property was unique.

Answer 3

I could not plan a monkey, as suggested in the answer of Rastislav.

This is what worked for me:

• Override the compileInsert method in a custom query grammar class that extends the scope of the MySqlGrammar class.

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

So the class code looks 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 class, and then inside the method, I just changed insert to insert ignore . Everything else is preserved.

Then, in a specific place in the code, in the application (scheduled task), where I needed to “insert ignore”, I simply did the following:

 <?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); } } 

Answer 4

Updated answer for Laravel Eloquent in 2018

It also handles multiple simultaneous inserts (instead of one record at a time).

Warning: Eric's comment below is probably correct. This code worked for my past project, but before using this code again, I would carefully look at it, add test cases and adjust the function until it always works as intended. It can be as simple as moving the TODO line down beyond the if brackets.

Put this in your model class or in the BaseModel class that extends your model:

 /** * @see https://stackoverflow.com/a/25472319/470749 * * @param array $arrayOfArrays * @return bool */ public static function insertIgnore($arrayOfArrays) { $static = new static(); $table = with(new static)->getTable(); //https://github.com/laravel/framework/issues/1436#issuecomment-28985630 $questionMarks = ''; $values = []; foreach ($arrayOfArrays as $k => $array) { if ($static->timestamps) { $now = \Carbon\Carbon::now(); $arrayOfArrays[$k]['created_at'] = $now; $arrayOfArrays[$k]['updated_at'] = $now; if ($k > 0) { $questionMarks .= ','; } $questionMarks .= '(?' . str_repeat(',?', count($array) - 1) . ')'; $values = array_merge($values, array_values($array));//TODO } } $query = 'INSERT IGNORE INTO ' . $table . ' (' . implode(',', array_keys($array)) . ') VALUES ' . $questionMarks; return DB::insert($query, $values); } 

Use like this:

Shop::insertIgnore([['name' => 'myShop'], ['name' => 'otherShop']]);

Answer 5

Not sure if anyone is useful, but I recently adapted the hakre approach to Laravel 5:

You need to modify the following 3 files for your Insert Ignore to work:

• In Builder.php (vendor / laravel / framework / src / illuminate / database / query / Builder.php) you need to clone the function insert, with a name change for insertIgnore and a change to the grammar of the function: $sql = $this->grammar->compileInsertIgnore($this, $values);)

• In Grammar.php (vendor / laravel / framework / src / illuminate / database / query / grammars / Grammar.php) you should clone the compileInsert function and rename it to compileInsertIgnore, where you change return: return "insert ignore into $table ($columns) values $parameters";

• In Connection.php (vendor / laravel / framework / src / illuminate / database / Connection.php) you just need to clone the insert function and rename it to insertIgnore

Now you have to do this, the connection can recognize the insertIgnore function, the builder can indicate the correct grammar, and the grammar includes “ignore” in the statement. Please do not work so well for MySQL, perhaps it is not so smooth for other databases.

Answer 6

Add the following insertIgnore method to your model

 <?php namespace App; use Illuminate\Auth\Authenticatable; use Illuminate\Database\Eloquent\Model; use Illuminate\Auth\Passwords\CanResetPassword; use Illuminate\Foundation\Auth\Access\Authorizable; use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract; use Illuminate\Contracts\Auth\Access\Authorizable as AuthorizableContract; use Illuminate\Contracts\Auth\CanResetPassword as CanResetPasswordContract; class User extends Model implements AuthenticatableContract, AuthorizableContract, CanResetPasswordContract { use Authenticatable, Authorizable, CanResetPassword; /** * The database table used by the model. * * @var string */ protected $table = 'users'; /** * The attributes that are mass assignable. * * @var array */ protected $fillable = ['name', 'email', 'password']; /** * The attributes excluded from the model JSON form. * * @var array */ protected $hidden = ['password', 'remember_token']; public static function insertIgnore(array $attributes = []) { $model = new static($attributes); if ($model->usesTimestamps()) { $model->updateTimestamps(); } $attributes = $model->getAttributes(); $query = $model->newBaseQueryBuilder(); $processor = $query->getProcessor(); $grammar = $query->getGrammar(); $table = $grammar->wrapTable($model->getTable()); $keyName = $model->getKeyName(); $columns = $grammar->columnize(array_keys($attributes)); $values = $grammar->parameterize($attributes); $sql = "insert ignore into {$table} ({$columns}) values ({$values})"; $id = $processor->processInsertGetId($query, $sql, array_values($attributes)); $model->setAttribute($keyName, $id); return $model; } } 

You can use:

 App\User::insertIgnore([ 'name' => 'Marco Pedraza', 'email' => ' [e-mail] ' ]); 

The following request will be executed:

 insert ignore into `users` (`name`, `email`, `updated_at`, `created_at`) values (?, ?, ?, ?) 

This method automatically adds / removes the “Eloquent” timestamps if you enable or disable it.

Answer 7

I finally found this https://github.com/yadakhov/insert-on-duplicate-key which really helped me

User :: insertIgnore ($ users); this is the method that I use, giving it an array of strings and returned strings

install it through composer: composer requires yadakhov / insert-on-duplicate-key

Answer 8

Ability to not write code: https://github.com/guidocella/eloquent-insert-on-duplicate-key

I just checked this - it works with my 5000 inserts at a time, sometimes with duplicates ...

With it, you will receive the following functions:

 User::insertOnDuplicateKey($data); User::insertIgnore($data); 

Answer 9

 $your_array = array('column' => 'value', 'second_column' => 'value'); DB::table('your_table')->insert($your_array); 

Keep in mind, I don’t know where your data comes from, but you should always sanitize it. If you have more than one entry, just go to the loop.

In INSERT IGNORE locate the INSERT method in a free library, create a new method called insert_ignore, just like inserting, and just change using IGNORE .