I need to update records for Guide Steps (Every recipe has steps/instruction and my table was created for storing this data)

I was able to create those records in the db with quite easy syntax:

GuideStep::insert($groupedSteps); // $groupedSteps is an array

But now I need to update those records (and remove unnecessary ones), for this moment I came up with logic that can only update records or create if there is no such records:

foreach ($groupedSteps as $step){
  GuideStep::updateOrInsert(
    ['recipe_id' => $recipeId, 'step_number' => $step['step_number']],
    ['step_text' => $step['step_text'], 'step_image' => $step['step_image']]
  );
}

migration:

Schema::create('guide_steps', function (Blueprint $table) {
            $table->id();
            $table->foreignId('recipe_id')->constrained()->cascadeOnDelete();
            $table->integer('step_number');
            $table->text('step_text');
            $table->string('step_image')->default('recipes-images/default/default_photo.png');
            $table->timestamps();
        });

I thought that I'll be able to use upsert() but this method requires unique columns (I don't have those)

Be grateful for some advices

I need to update records for Guide Steps (Every recipe has steps/instruction and my table was created for storing this data)

I was able to create those records in the db with quite easy syntax:

GuideStep::insert($groupedSteps); // $groupedSteps is an array

But now I need to update those records (and remove unnecessary ones), for this moment I came up with logic that can only update records or create if there is no such records:

foreach ($groupedSteps as $step){
  GuideStep::updateOrInsert(
    ['recipe_id' => $recipeId, 'step_number' => $step['step_number']],
    ['step_text' => $step['step_text'], 'step_image' => $step['step_image']]
  );
}

migration:

Schema::create('guide_steps', function (Blueprint $table) {
            $table->id();
            $table->foreignId('recipe_id')->constrained()->cascadeOnDelete();
            $table->integer('step_number');
            $table->text('step_text');
            $table->string('step_image')->default('recipes-images/default/default_photo.png');
            $table->timestamps();
        });

I thought that I'll be able to use upsert() but this method requires unique columns (I don't have those)

Be grateful for some advices

• php
• laravel
• eloquent

Share Improve this question Follow edited Mar 26 at 8:03 Dmytro Shved asked Mar 26 at 7:01 Dmytro ShvedDmytro Shved 3377 bronze badges 0

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

I have also came up with approach when I am deleting all records (all steps) and inserting them:

if ($this->recipeId != 0) {
    GuideStep::where('recipe_id', $recipeId)->delete();
}
 GuideStep::insert($groupedSteps);

What do you think?

You must update existing steps, create new ones, and remove unnecessary ones. Since your guide_steps table does not have a unique constraint on recipe_id and step_number. You can't use upsert(). Instead, you can try the following approach:

• Fetch existing steps for the given recipe_id.

• Determine which steps need to be updated, inserted, or deleted.

• Perform batch updates, inserts, and deletions efficiently.

Code Example:

use App\Models\GuideStep;
use Illuminate\Support\Facades\DB;

function updateGuideSteps($recipeId, $groupedSteps) {
    // Fetch existing steps
    $existingSteps = GuideStep::where('recipe_id', $recipeId)
        ->pluck('id', 'step_number') 
        ->toArray();

    $newStepNumbers = [];
    $insertData = [];
    $updateData = [];

    foreach ($groupedSteps as $step) {
        $stepNumber = $step['step_number'];
        $newStepNumbers[] = $stepNumber;

        if (isset($existingSteps[$stepNumber])) {
            // Prepare update data
            $updateData[] = [
                'id' => $existingSteps[$stepNumber],
                'step_text' => $step['step_text'],
                'step_image' => $step['step_image'],
                'updated_at' => now(),
            ];
        } else {
            // Prepare insert data
            $insertData[] = [
                'recipe_id' => $recipeId,
                'step_number' => $stepNumber,
                'step_text' => $step['step_text'],
                'step_image' => $step['step_image'],
                'created_at' => now(),
                'updated_at' => now(),
            ];
        }
    }

    // Perform batch insert
    if (!empty($insertData)) {
        GuideStep::insert($insertData);
    }

    // Perform batch update
    if (!empty($updateData)) {
        $table = (new GuideStep)->getTable();
        $cases = [];
        $ids = [];
        $bindings = [];

        foreach ($updateData as $data) {
            $id = (int) $data['id'];
            $ids[] = $id;
            $cases[] = "WHEN id = ? THEN ?";
            $bindings[] = $id;
            $bindings[] = $data['step_text'];
        }

        $sql = "UPDATE {$table} SET step_text = CASE " . implode(" ", $cases) . " END WHERE id IN (" . implode(',', $ids) . ")";
        DB::update($sql, $bindings);
    }

    // Delete steps that are not in the new set
    GuideStep::where('recipe_id', $recipeId)
        ->whereNotIn('step_number', $newStepNumbers)
        ->delete();
}

The upsert() method as per documentation requires a unique identifier. Looking by the way you are updating or creating your GuideStep you'd need something of the sort:

foreach ($groupedSteps as $step){
  GuideStep::upsert(
    [
        'recipe_id' => $recipeId, 
        'step_number' => $step['step_number']
        'step_text' => $step['step_text']
        'step_image' => $step['step_image']
    ],
    uniqueBy: ['recipe_id', 'step_number'],
    update: ['step_text', 'step_image']
  );
}

You can check more on how it is used here.
Deleting then creating instead of updating is a very bad idea due to a couple of reasons, you can check this discussion.

I haven't tested the code but it should work right off the bat.

Also, make sure to always validate your request in the controller, so that no surprise data comes in