Laravel 5.1 创建或更新重复

Laravel 5.1 Create or Update on Duplicate

提问by Neel

In Laravel 5.1, for MySQL insert, I want to see if the record already exists and update on duplicate or create new if none exists.

在 Laravel 5.1 中，对于 MySQL 插入，我想查看记录是否已存在并更新重复或如果不存在则创建新记录。

I have already searched SO where the answers were for old laravel versions. In one of the old topics it said that a new updateOrCreate()method was added in core last year. But when I try that, I am getting the error:

我已经搜索过答案是旧的 laravel 版本。在一个旧主题中，它说updateOrCreate()去年在核心中添加了一种新方法。但是当我尝试这样做时，我收到错误消息：

Integrity constraint violation: 1062 Duplicate entry '1' for key 'app_id' 

This is the query I use:

这是我使用的查询：

AppInfo::updateOrCreate(array(
    'app_id' => $postData['appId'],
    'contact_email' => $postData['contactEmail']
));

Where app_idis the unique foreign key in that table and I want to update the record if exists or create a new one. I have tried searching the 5.1 docs and couldn't find the info I need. Can someone guide me here please...

app_id该表中唯一的外键在哪里，我想更新记录（如果存在）或创建一个新记录。我尝试搜索 5.1 文档，但找不到我需要的信息。有人可以在这里指导我吗...

回答by MrPandav

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

根据 Eloquent 模型方法“updateOrCreate()”的定义

function updateOrCreate(array $attributes, array $values = []){}

函数 updateOrCreate(array $attributes, array $values = []){}

it takes two argument ...

它需要两个参数......

1. one is the attributes using which you want to check in database if the record is present
2. second is the new attribute values that you want to create or update

1. 一个是如果记录存在，您想在数据库中使用的属性
2. 第二个是您要创建或更新的新属性值

---

---

AppInfo::updateOrCreate(['app_id' => $postData['appId']],
                        ['contact_email' => $postData['contactEmail']]);

回答by Hayk

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:

我正在回答这个问题，因为我找不到任何与 ON DUPLICATE KEY UPDATE 相关的答案，尽管我使用的是Laravel 5.4。如果您查看 Laravel 核心代码中的 updateOrCreate 方法，您会发现毕竟 Laravel 使用了两种不同的查询：一种用于更新，另一种用于创建。因此，有时您会在数据库中获得重复的数据。因此，在某些情况下，编写这种原始查询会很有用：

DB::statement("INSERT INTO table_name(col_1, col_2) VALUES (?, ?) ON DUPLICATE KEY UPDATE col_1= col_1+ 1", ([val_1, val_2]));

DB::statement("INSERT INTO table_name( col_1, col_2) VALUES (?, ?) ON DUPLICATE KEY UPDATE col_1= col_1+ 1", ([val_1, val_2]));

Hope it can be useful for someone.

希望它对某人有用。

回答by Yada

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

我创建了一个包来处理重复键上的 MySQL 插入。

It may be useful for others:

它可能对其他人有用：

https://packagist.org/packages/yadakhov/insert-on-duplicate-key

https://packagist.org/packages/yadakhov/insert-on-duplicate-key

Example:

例子：

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

// associative array must match column names
$users = [
    ['id' => 1, 'email' => '[email protected]', 'name' => 'User One'],
    ['id' => 2, 'email' => '[email protected]', 'name' => 'User Two'],
    ['id' => 3, 'email' => '[email protected]', 'name' => 'User Three'],
];

User::insertOnDuplicateKey($users);

回答by Marco Pedraza

Add the follow method insertUpdate to your Model

将以下方法 insertUpdate 添加到您的模型

<?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;
    }
}

You can use:

您可以使用：

App\User::insertUpdate([
    'name' => 'Marco Pedraza',
    'email' => '[email protected]'
]);

The next query it will be executed:

将执行下一个查询：

insert into `users` (`name`, `email`, `updated_at`, `created_at`) values (?, ?, ?, ?) on duplicate key update `id` = LAST_INSERT_ID(id),`name` = 'Marco Pedraza',`email` = '[email protected]',`updated_at` = '2016-11-02 01:30:05',`created_at` = '2016-11-02 01:30:05'

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

如果您已启用或禁用，该方法会自动添加/删除 Eloquent 时间戳。

回答by Shanka SMS

To use laravel function updateOrCreateyou need auto increment idin your table.

要使用 Laravel 函数updateOrCreate，您需要在表中自动增加 id。

what they are doing is

他们正在做的是

select id from your_table where your_attributes

select id from your_table where your_attributes

after that get auto increment id

之后得到自动增量id

then

然后

update your_table set your_values where field_id

update your_table set your_values where field_id

回答by Ben Wilson

In my case this was caused by a simple mismatch on the auto incrementing key in my model table.

在我的情况下，这是由模型表中自动递增键的简单不匹配引起的。

Getting the current max id:

获取当前最大 ID：

SELECT max(id) from companies

Then setting the sequence value one higher:

然后将序列值设置得更高：

select setval('companies_id_seq', 164);

eliminated the error.

消除了错误。