Add INSERT IGNORE support to Query Builder

[gephaest]

It would be very useful to have ability to use IGNORE statement like this
DB::connection('foo')->table('bar')->insert($rows)->ignore();

For additional details check MySQL docs: http://dev.mysql.com/doc/refman/5.6/en/insert.html

[ArjanSchouten]

Yes but is it supported by mssql, postgresql and sqlite?

[dtaub]

A quick google shows that SQLite supports a somewhat similar construct but for the other DBMS's you would need to either use WHERE NOT EXISTS or some form of exception catching. WHERE NOT EXISTS would probably be the most cross-compatible way to go about it.

[gephaest]

ignore() can be alias to WHERE NOT EXISTS. Ignore looks clear and understandable.

[dtaub]

Also, you can't chain onto insert() since that method actually runs the statement, so it would have to be something more like:

DB::connection('foo')->table('bar')->insertIgnore($rows);

[GrahamCampbell]

You probably just want to use a raw query here.

[jfoliveira]

Using raw query is fine for inserting a single row/array.

However, as the insert function supports an array of multiple rows to be inserted at a time - insert($multipleRows) - it would be very convenient to have INSERT IGNORE INTO implemented in the QueryBulider.

[jfoliveira]

A good soul has published a package that can add this capability to a model: https://github.com/jdavidbakr/replaceable-model

[jbruni]

Here is what worked for me: https://stackoverflow.com/a/45151709/370290

[phpguru]

Because this very old issue is the # 1 hit on google for insert ignore query builder... what works best for us is firstOrCreate.

[mtrajano]

firstOrCreate is great if you only need to insert one row, but like someone else said the great thing about insert is that it lets you pass multiple rows at once, I still think a insertIgnore would be a good addition

[gpressutto5]

@phpguru firstOrCreate is for Eloquent. Query builder isn't the same as Eloquent.

[dunhamjared]

@GrahamCampbell Could we re-open this? Is there anyway we could vote on this feature? Judging from the responses on this issue, this feature would be very useful for many people who use databases that allow insert ignore.

[dunhamjared]

Found this great Laravel extension that adds support for INSERT & UPDATE (UPSERT) and INSERT IGNORE to the query builder and Eloquent: staudenmeir/laravel-upsert

[dunhamjared]

Laravel's Query Builder now has insertOrIgnore in version v5.8.33 and up:

<?php
DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => 'taylor@example.com'],
    ['id' => 2, 'email' => 'dayle@example.com']
]);

Read more here: https://laravel.com/docs/5.8/queries#inserts

[iwasherefirst2]

@dunhamjared thank you! Exactly what I was looking for.