1062 Duplicate entry 'General' - skip the insert if there is already a category with same-CodePudding

I have a migration post_categories like this:

 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();
}

And in a Laravel nova Resource in the fields method there is some code that stores on the above table some categories that are being returned from the API request:

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'])
            );
        } 
        
}

And it works, however I'm getting this error:

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'

Do you know how to, if there are already a category name stored in the table just skip that insert, to avoid these kind of errors?

CodePudding user response：

There are many methods that accomplish what you want using Query Builder

insertOrIgnore

Here is an example of this method:

$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);

upsert

Here is an example of this method:

$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

Here is an example of this method:

$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]);

There are many methods also that accomplish what you want using Eloquent and you'll find many examples in this section