Eloquent mass insert, ON DUPLICATE KEY UPDATE

Question

I was doing:

$data = [
        ['amodule'=>'amodule', 'akey'=>'first_example', 'avalue'=>'4096', 'created_at'=>'2014-09-21'],
        ['amodule'=>'amodule2', 'akey'=>'sec_example', 'avalue'=>'4097', 'created_at'=>'2014-09-22'],
        ['amodule'=>'amodule2', 'akey'=>'sec_example', 'avalue'=>'4097', 'created_at'=>'2014-09-22'],
    ];

    Models\Snapshot::insert($data);

Just performing a mass insert. Now i want to add an ON DUPLICATE KEY to this. Any idea on how to do it? Or at list to ignore duplicates?

Thanks in advance...

Answer 1

Eloquent does not support this at the moment, you'd have to write it as a raw query.

// generates (?,?,?,?),(?,?,?,?),(?,?,?,?),(?,?,?,?)
$valueString = implode(',', array_fill(0, count($data), '(' . implode(',', array_fill(0, count($data[0]), '?')) . ')')); 
$values = [];

// Flattens the array
foreach($data as $row) {
    foreach($row as $value) {
        $values[] = $value;
    }
}

// Perform the insert
\DB::insert(
    "insert into `snapshots` (`amodule`, `akey`, `avalue`, `created_at`) values {$values} on duplicate key update",
    $values
);

Keep in mind that in order for on duplicate key update to trigger, at least one of the inserted values must have a primary key or an unique key.

Answer 2

If you would like to perform mass inserts in a single query, then you should use the upsert method. The method's first argument consists of the values to insert or update, while the second argument lists the column(s) that uniquely identify records within the associated table. The method's third and final argument is an array of the columns that should be updated if a matching record already exists in the database.

Snapshot::upsert([
        ['amodule'=>'amodule', 'akey'=>'first_example', 'avalue'=>'4096', 'created_at'=>'2014-09-21'],
        ['amodule'=>'amodule2', 'akey'=>'sec_example', 'avalue'=>'4097', 'created_at'=>'2014-09-22'],
        ['amodule'=>'amodule2', 'akey'=>'sec_example', 'avalue'=>'4097', 'created_at'=>'2014-09-22'],
    ], ['amodule', 'akey'], ['avalue']);

if you just give two arguments then the second argument is the column that needs to be updated in case a duplicate is found.

Snapshot::upsert([
        ['amodule'=>'amodule', 'akey'=>'first_example', 'avalue'=>'4096', 'created_at'=>'2014-09-21'],
        ['amodule'=>'amodule2', 'akey'=>'sec_example', 'avalue'=>'4097', 'created_at'=>'2014-09-22'],
        ['amodule'=>'amodule2', 'akey'=>'sec_example', 'avalue'=>'4097', 'created_at'=>'2014-09-22'],
    ], ['avalue']);

laravel however recommends giving all three arguments for avoiding any possible eloquent mass assignment error. the above method will create this query

    insert into `snapshots` (`amodule`, `akey`, `avalue`) values (amodule, amodule, 4096) on duplicate key update 
`amodule` = values(`amodule`), `akey` = values(`akey`),
 `avalue` = values(`avalue`))" 

The upsert method will automatically set the created_at and updated_at timestamps if timestamps are enabled on the model: