Rectangle 27 0

Laravel 5.1 Create or Update on Duplicate?


<?php

namespace App;

use Illuminate\Auth\Authenticatable;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Auth\Passwords\CanResetPassword;
use Illuminate\Foundation\Auth\Access\Authorizable;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;
use Illuminate\Contracts\Auth\Access\Authorizable as AuthorizableContract;
use Illuminate\Contracts\Auth\CanResetPassword as CanResetPasswordContract;

class User extends Model implements AuthenticatableContract,
                                    AuthorizableContract,
                                    CanResetPasswordContract
{
    use Authenticatable, Authorizable, CanResetPassword;

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'users';

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = ['name', 'email', 'password'];

    /**
     * The attributes excluded from the model's JSON form.
     *
     * @var array
     */
    protected $hidden = ['password', 'remember_token'];


    public static function insertUpdate(array $attributes = [])
    {
        $model = new static($attributes);

        $model->fill($attributes);

        if ($model->usesTimestamps()) {
            $model->updateTimestamps();
        }

        $attributes = $model->getAttributes();

        $query = $model->newBaseQueryBuilder();
        $processor = $query->getProcessor();
        $grammar = $query->getGrammar();

        $table = $grammar->wrapTable($model->getTable());
        $keyName = $model->getKeyName();
        $columns = $grammar->columnize(array_keys($attributes));
        $insertValues = $grammar->parameterize($attributes);

        $updateValues = [];

        if ($model->primaryKey !== null) {
            $updateValues[] = "{$grammar->wrap($keyName)} = LAST_INSERT_ID({$keyName})";
        }

        foreach ($attributes as $k => $v) {
            $updateValues[] = sprintf("%s = '%s'", $grammar->wrap($k), $v);
        }

        $updateValues = join(',', $updateValues);

        $sql = "insert into {$table} ({$columns}) values ({$insertValues}) on duplicate key update {$updateValues}";

        $id = $processor->processInsertGetId($query, $sql, array_values($attributes));

        $model->setAttribute($keyName, $id);

        return $model;
    }
}
App\User::insertUpdate([
    'name' => 'Marco Pedraza',
    'email' => 'mpdrza@gmail.com'
]);
insert into `users` (`name`, `email`, `updated_at`, `created_at`) values (?, ?, ?, ?) on duplicate key update `id` = LAST_INSERT_ID(id),`name` = 'Marco Pedraza',`email` = 'mpdrza@gmail.com',`updated_at` = '2016-11-02 01:30:05',`created_at` = '2016-11-02 01:30:05'

Hi, welcome to SO. Please don't just dump code, explain your train of thought so people can better understand your answer. Thanks.

The method automatically add/remove the Eloquent timestamps if you have enabled or disabled.

Useful function that seems to be working, unlike the updateOrCreate function this one also updates the timestamps. However why does it update both? It should only update the updated_at, currently it also updates the created_at. And is there any way to use this function with bulk inserts? That's why I came looking for this otherwise the updateOrCreate function seemed to work ok aside from the fact it didn't automatically update the timestamps.

Note
Rectangle 27 0

Laravel 5.1 Create or Update on Duplicate?


AppInfo::updateOrCreate(['app_id' => $postData['appId']],
                        ['contact_email' => $postData['contactEmail']]);
  • one is the attributes using which you want to check in database if the record is present
  • second is the new attribute values that you want to create or update

@Neel : Whenever you don't find any documentation online (usually the case for cutting edge beta frameworks) or in doubt, open up their source code file,,they always have very nice documentation and scenario explained before each functions defination

As per the Definition of the Eloquent Model Method "updateOrCreate()"

If I still haven't created the record on the database, how do I have the id?

Perfect! That is exactly what I was doing wrong. I presumed setting the unique key in database should take of it automatically but I was wrong. Thank you so much for pointing that out @MrPandav It works well now after adding 2 arguments to it. :)

it can be any column for which you want to check in database, i.e. name, email..etc if row with that value is found, the data in that row will be updated wit new data that you have provided else new record will be created

Note
Rectangle 27 0

Laravel 5.1 Create or Update on Duplicate?


col_1
col_2
table_name

I am answering to this question cause I can't find any answer related to ON DUPLICATE KEY UPDATE, although I am using Laravel 5.4. If you look at the updateOrCreate method in the Laravel's core code, you will see after all Laravel is using 2 different queries: one for update and another one for create. Because of this, sometimes you can get duplicated data in the DB. So in some cases it can be useful to write this kind of raw query:

Note
Rectangle 27 0

Laravel 5.1 Create or Update on Duplicate?


/**
 * Class User.
 */
class User extends Model
{
    use Yadakhov\InsertOnDuplicateKey;
    ...
}

// associative array must match column names
$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'],
];

User::insertOnDuplicateKey($users);

I created a package to work with MySQL insert on duplicate key.

Note