wwardaww/insert-on-duplicate-key

A trait for MySQL INSERT ON DUPLICATE KEY UPDATE.

1.2.6 2018-01-29 15:42 UTC

README

This Trait forked from yadakhov/ insert-on-duplicate-key(v1.2.0)

Latest Stable Version License

Orginal packet status :

Latest Stable Version License Build Status

Insert Duplicate Key Update is a quick way to do mass insert.

It's a trait meant to be used with Laravel's Eloquent ORM.

Installing

composer require wwardaww/insert-on-duplicate-key

Code Example

use Illuminate\Database\Eloquent\Model;
use Wwardaww\InsertOnDuplicateKey;

/**
 * Class User.
 */
class User extends Model
{
    // The function is implemented as a trait.
    use InsertOnDuplicateKey;
}

Multi values insert.

    $users = [
        ['id' => 1, 'email' => 'user1@email.com', 'name' => 'User One'],
        ['id' => 2, 'email' => 'user2@email.com', 'name' => 'User Two'],
        ['id' => 3, 'email' => 'user3@email.com', 'name' => 'User Three'],
    ];

INSERT ON DUPLICATE KEY UPDATE ANOTHER SCHEME

    User::insertOnDuplicateKey($users,null,'anotherScheme');
    -- produces this query
    INSERT INTO `anotherScheme`.`users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three')
    ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `email` = VALUES(`email`), `name` = VALUES(`name`)

INSERT ON DUPLICATE KEY UPDATE DEFAULT SCHEME

    User::insertOnDuplicateKey($users,null);
    -- produces this query
    INSERT INTO `homestead`.`users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three')
    ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `email` = VALUES(`email`), `name` = VALUES(`name`)
    User::insertOnDuplicateKey($users, ['email']);
    -- produces this query
    INSERT INTO `users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three')
    ON DUPLICATE KEY UPDATE `email` = VALUES(`email`)

INSERT IGNORE

    User::insertIgnore($users);
    -- produces this query
    INSERT IGNORE INTO `users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three');

REPLACE INTO

    User::replace($users);
    -- produces this query
    REPLACE INTO `users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three');

created_at and updated_at fields.

created_at and updated_at will not be updated automatically. To update you can pass the fields in the insert array.

['id' => 1, 'email' => 'user1@email.com', 'name' => 'User One', 'created_at' => Carbon::now(), 'updated_at' => Carbon::now()]

Run unit tests

./vendor/bin/phpunit

Will this work on Postgresql?

No. On Duplicate Key Update is only available on MySQL. Postgresql 9.4 has a similar feature called UPSERT. Implementing UPSERT is left as an exercise for the reader.

Isn't this the same as updateOrCreate()?

It is similar but not the same. The updateOrCreate() will only work for one row at a time which doesn't allow bulk insert. InsertOnDuplicateKey will work on many rows.