1062 重复条目"常规" - 如果已经有同名的类别，则跳过插入

Question

我有一个迁移post_categories:

public function up()
{
Schema::create('post_categories', function (Blueprint $table) {
$table->id();

$table->foreignId('post_id')
->nullable()
->constrained('posts');
$table->unsignedSmallInteger('category_id')->nullable();
$table->string('category_name')->nullable();
}    

在Laravel nova Resource的fields方法中，有一些代码存储在上面的表中，这些表是从API请求返回的一些类别:

public function fields(Request $request)
{
$postInformation = (new postInformation)->handle(['field' => 'slug', 'value' => $this->slug]);
$postId =  $postInformation['id'];
try{
DB::table('post_categories')->insert(
array_map(fn ($category) => [
'post_id' => $postId,
'category_id' => $category['id'],
'category_name' => $category['name']['en_gb'],
], $postInformation['categories']['data'])
);
} 

}

它工作，但我得到这个错误:

exception: "Illuminate\Database\QueryException"
file: "/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php"
line: 742
message: "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'General'

你知道如何，如果已经有一个类别名称存储在表中只是跳过插入，以避免这类错误吗?

Answer 1

有许多方法可以使用Query Builder完成您想要的功能

insertOrIgnore

下面是这个方法的一个例子:

$data = array_map(fn ($category) => [
'post_id' => $postId,
'category_id' => $category['id'],
'category_name' => $category['name']['en_gb'],
], $postInformation['categories']['data']);
DB::table('post_categories')->insertOrIgnore($data);

插入

下面是这个方法的一个例子:

$data = array_map(fn ($category) => [
'post_id' => $postId,
'category_id' => $category['id'],
'category_name' => $category['name']['en_gb'],
], $postInformation['categories']['data']);
DB::table('post_categories')->upsert($data, ['the_column_must_be_unique'], ['the_columns_you_want_update_here']);

updateOrInsert

下面是这个方法的一个例子:

$data = array_map(fn ($category) => [
'post_id' => $postId,
'category_id' => $category['id'],
'category_name' => $category['name']['en_gb'],
], $postInformation['categories']['data']);
DB::table('post_categories')->updateOrInsert($data, ['the_columns_you_want_update_here' => $postId]);

也有许多方法可以实现使用Eloquent的目的，您将在本节中找到许多示例