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

Question

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?

Answer 1

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