Skip to content
Laravel UPSERT and INSERT IGNORE queries
Branch: master
Clone or download
Latest commit 0f60dee May 19, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
src Add 1.0 May 22, 2019
tests Support Laravel 5.8 May 22, 2019
.gitattributes Add 1.0 May 22, 2019
.gitignore Add 1.0 May 22, 2019
.scrutinizer.yml Add 1.0 May 22, 2019
.travis.yml Support Laravel 5.8 May 22, 2019
LICENSE Initial commit May 18, 2019
README.md Support Laravel 5.8 May 22, 2019
composer.json
phpunit.xml.dist Add 1.0 May 22, 2019

README.md

Build Status Code Coverage Scrutinizer Code Quality Latest Stable Version Total Downloads License

Introduction

This Laravel extension adds support for INSERT & UPDATE (UPSERT) and INSERT IGNORE to the query builder and Eloquent.

Supports Laravel 5.5+.

Compatibility

Installation

composer require staudenmeir/laravel-upsert:"^1.0"

Usage

INSERT & UPDATE (UPSERT)

Consider this users table with a unique username column:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('username')->unique();
    $table->boolean('active');
    $table->timestamps();
});

Use upsert() to insert a new user or update the existing one. In this example, an inactive user will be reactivated and the updated_at timestamp will be updated:

DB::table('users')->upsert(
    ['username' => 'foo', 'active' => true, 'created_at' => now(), 'updated_at' => now()],
    'username',
    ['active', 'updated_at']
);

Provide the values to be inserted as the first argument. This can be a single record or multiple records.

The second argument is the column(s) that uniquely identify records. All databases except SQL Server require these columns to have a PRIMARY or UNIQUE index.

Provide the columns to be the updated as the third argument (optional). By default, all columns will be updated. You can provide column names and key-value pairs with literals or raw expressions (see below).

As an example with a composite key and a raw expression, consider this table that counts visitors per post and day:

Schema::create('stats', function (Blueprint $table) {
    $table->unsignedInteger('post_id');
    $table->date('date');
    $table->unsignedInteger('views');
    $table->unique(['post_id', 'date']);
});

Use upsert() to log visits. The query will create a new record per post and day or increment the existing view counter:

DB::table('stats')->upsert(
    [
        ['post_id' => 1, 'date' => now()->toDateString(), 'views' => 1],
        ['post_id' => 2, 'date' => now()->toDateString(), 'views' => 1],
    ],
    ['post_id', 'date'],
    ['views' => DB::raw('stats.views + 1')]
);

INSERT IGNORE

You can also insert records while ignoring duplicate-key errors:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('username')->unique();
    $table->timestamps();
});

DB::table('users')->insertIgnore([
    ['username' => 'foo', 'created_at' => now(), 'updated_at' => now()],
    ['username' => 'bar', 'created_at' => now(), 'updated_at' => now()],
]);

SQL Server requires a second argument with the column(s) that uniquely identify records:

DB::table('users')->insertIgnore(
    ['username' => 'foo', 'created_at' => now(), 'updated_at' => now()],
    'username'
);

Eloquent

You can use UPSERT and INSERT IGNORE queries with Eloquent models.

In Laravel 5.5–5.7, this requires the HasUpsertQueries trait:

class User extends Model
{
    use \Staudenmeir\LaravelUpsert\Eloquent\HasUpsertQueries;
}

User::upsert(['username' => 'foo', 'active' => true], 'username', ['active']);

User::insertIgnore(['username' => 'foo']);

If the model uses timestamps, upsert() and insertIgnore() will automatically add timestamps to the inserted values. upsert() will also add updated_at to the updated columns.

Lumen

If you are using Lumen, you have to instantiate the query builder manually:

$builder = new \Staudenmeir\LaravelUpsert\Query\Builder(app('db')->connection());

$builder->from(...)->upsert(...);

In Eloquent, the HasUpsertQueries trait is required for all versions of Lumen.

You can’t perform that action at this time.