Create your own `upsert` method

Occasionally, you may need to update an existing record or create a new record if no matching record exists. You will wish to conduct it by one statement, thus UPSERT is your solution

UPSERT is one of the essential features of DBMS software for managing the database. This operation allows the DML users to insert a new record or update existing data into a table. An UPSERT is made up of a combination of two words named UPDATE and INSERT. The first two letters, i.e., UP stands for UPDATE while the SERT stands for INSERT.

The UPSERT is an atomic operation that means it is an operation that completes in a single-step. For example, if a record is new, it will trigger an INSERT command. But, if it already exists in the table, then this operation will perform an UPDATE statement. By default, MySQL provides the ON DUPLICATE KEY UPDATE option to INSERT, which accomplishes this task.

The INSERT ON DUPLICATE KEY UPDATE statement is a non-destructive method that means it does not remove the duplicate row. Instead, when we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updating the existing row occurs.

The syntax of Insert on Duplicate Key Update statement in MySQL is given below:

INSERT INTO table (column_names) 
VALUES (data) 
ON DUPLICATE KEY UPDATE 
column1 = expression, column2 = expression…;

Based on this idea, you can apply to create your own upsert() method in Laravel project. Let do it now!

In this example, if an user exists with fullname is Dean Ngo, and email is [email protected], his age and address columns will be updated. If no such user exists, a new user will be created. Especially, you wish to perform multiple records in one query

$data = [
    ['fullname' => 'Dean Ngo', 'email' => '[email protected]', 'age' => 20, 'address' => 'Danang'],
    ['fullname' => 'Bean Roman', 'email' => '[email protected]', 'age' => 25, 'address' => 'Countryard'],
];

To implement a way in reusing code, you have to use Trait

// App\Traits\QueryKit.php
<?php

namespace App\Traits;

use Illuminate\Support\Facades\DB;

trait QueryKit
{
    /**
     * Insert new rows or update existed rows
     *
     * @param array $data
     * @param array $insertKeys
     * @param array $updateKeys
     *
     * @return void
     */
    public static function insertDuplicate(array $data, array $insertKeys, array $updateKeys)
    {
        $model = new static;
        $query = "INSERT INTO {$model->getTable()} __INSERTKEYS__ VALUES __INSERTVALUE__ ON DUPLICATE KEY UPDATE __UPDATEVALUES__";
        $tmpInKeys = array_fill_keys($insertKeys, null);
        $tmpUpKeys = array_fill_keys($updateKeys, null);

        try {
            DB::beginTransaction();
            foreach ($data as $item) {
                $insertValue = array_intersect_key($item, $tmpInKeys);

                $updateValue = implode(', ', array_map(
                    function ($v, $k) { return sprintf("`%s`='%s'", $k, $v); },
                    array_intersect_key($item, $tmpUpKeys),
                    $updateKeys
                ));

                $statement = str_replace(
                    ['__INSERTKEYS__', '__INSERTVALUE__', '__UPDATEVALUES__'],
                    ["(`" . implode("`,`", $insertKeys) . "`)", "('" . implode("','", $insertValue) . "')", $updateValue],
                    $query
                );
                DB::statement($statement);
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            report($e);
            throw new \Exception($e->getMessage());
        }
    }
}

insertDuplicate(array $data, array $insertKeys, array $updateKeys)

First argument consists of the values to insert or update

Second argument lists the column(s) that uniquely identify records within the associated table

Third argument is an array of the columns that should be updated if a matching record already exists in the database.

// App\Models\User.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use App\Guardian\Traits\QueryKit;
class User extends Model
{
    use SoftDeletes;
    use QueryKit;
    protected $table = "users";
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['fullname', 'email', 'age', 'address'];
}

OK, you can try it now!

$data = [
    ['fullname' => 'Dean Ngo', 'email' => '[email protected]', 'age' => 20, 'address' => 'Danang'],
    ['fullname' => 'Bean Roman', 'email' => '[email protected]', 'age' => 25, 'address' => 'Countryard'],
];
\App\Models\User::insertDuplicate(
        $data,
        ['fullname', 'email'],
        ['age', 'address']
    );

The statement first attempts to insert a new row into the table. When a duplicate entry is their error occurs, MySQL will update the existing row with the value specified in the ON DUPLICATE KEY UPDATE clause.