Batch Creating/Updating intermediate pivot attributes

I'm building a meal planner that automatically builds me a shopping list and one of the key things is being able to set the quantity of an ingredient on a recipe.

I'm doing this by using intermediate values on my pivot between Recipe -> Ingredients.

This is defined in my intermediate pivot model IngredientRecipe which stores a unit & quantity that the recipe needs.

Recipe -> IngredientRecipe(Quantity,Unit) -> Ingredient

The issue I'm having is setting the quantity and unit for each individual ingredient. I know that I could just loop and iterate through each one individually after they're created however that just feels wrong and I'd rather do it in a single query.

I'm a bit stuck on how to proceed with this, I would have thought intialising a bunch of IngredientRecipe pivots with their values set and just saving them to the Recipe would be enough but the pivot values aren't preserved.

This is all built with Laravel 9.2 on PHP 8.1.

TIA

Recipe.php

    ...

    public function ingredients(): BelongsToMany
    {
        return $this->belongsToMany(Ingredient::class)
            ->using(IngredientRecipe::class);
    }

IngredientRecipe.php

    ...
    
    protected $fillable = [
        'recipe_id',
        'ingredient_id',
        'quantity',
        'unit_id',
    ];

    public function recipe(): BelongsTo
    {
        return $this->belongsTo(Recipe::class);
    }

    public function ingredient(): BelongsTo
    {
        return $this->belongsTo(Ingredient::class);
    }

    public function unit(): BelongsTo
    {
        return $this->belongsTo(Unit::class);
    }

UpdateIngredientRecipe.php

    ...

        return [
            'unit' => 'required|array',
            'quantity' => 'required|array',
            'unit.*' => 'integer|exists:units,id',
            'quantity.*' => 'numeric|digits_between:0,999',
        ];

IngredientRecipeController.php

    ...

public function update(Recipe $recipe, UpdateIngredientRecipe $request): RedirectResponse
    {
        $pivots = collect($request->validated('quantity'))
            ->map(function (float $quantity, int $ingredient_id) use ($request, $recipe): IngredientRecipe {

                $payload  = [
                    'ingredient_id' => $ingredient_id,
                    'recipe_id' => $recipe->id,
                    'quantity' => $quantity,
                    'unit_id'  => (int) $request->validated('unit')[$ingredient_id],
                ];

                return IngredientRecipe::make($payload);
            });

        $recipe->ingredients()->attach($pivots->pluck('ingredient_id'), $pivots->first()->toArray());
        dd(__METHOD__, $recipe->fresh('ingredients')->ingredients);

        dd(__METHOD__, $recipe->ingredients);
        return redirect()->route('recipe.get', $recipe);
    }

CodePudding user response：

So I've solved this by myself. Seems painfully obvious in retrospect but I hope this helps someone who gets stuck on a similar thing in future.

I'm now building an array of the pivot attributes keyed by the Ingredient ID

    public function update(Recipe $recipe, UpdateIngredientRecipe $request): RedirectResponse
    {
        $payload = [];
        $pivots = collect($request->validated('quantity'))
            ->each(function (float $quantity, int $ingredient_id) use ($request, $recipe, &$payload) {

                $payload[$ingredient_id] = [
                    'recipe_id' => $recipe->id,
                    'quantity' => $quantity,
                    'unit_id'  => (int) $request->validated('unit')[$ingredient_id],
                ];
            });

        dump($recipe->ingredients()->sync($payload));
        dd($recipe->fresh()->ingredients);

        return redirect()->route('recipe.get', $recipe);
    }

so in a sense what I'm doing now is:

$ingredientsToSync = [
    $ingredient_id => [
        'recipe_id' => $recipe->id,
        'quantity' => $quantity,
        'unit_id'  => (int) $request->validated('unit')[$ingredient_id],
    ], 
    ... // other ingredients below
];

$recipe->ingredients()->sync($ingredientsToSync);

It's also worth noting that I needed to update my relationship definition in Recipe in order to get the pivot values

    // Recipe.php
    ...
    public function ingredients(): BelongsToMany
    {
        return $this->belongsToMany(Ingredient::class)
            ->using(IngredientRecipe::class)
            ->withPivot(['quantity', 'unit_id']);
    }

CodePudding user response：

This should be sufficient for your use case:

public function update(Recipe $recipe, UpdateIngredientRecipe $request): RedirectResponse
    {
        $pivots = collect($request->validated('quantity'))
            ->map(function (float $quantity, int $ingredient_id) use ($request, $recipe): IngredientRecipe {

                return [
                    'ingredient_id' => $ingredient_id,
                    'recipe_id' => $recipe->id,
                    'quantity' => $quantity,
                    'unit_id'  => (int) $request->validated('unit')[$ingredient_id],
                ];
        });
        IngredientRecipe::createMany($pivots);

        return redirect()->route('recipe.get', $recipe);
    }

Since the relationship data is already in the pivot array you don't need to attach them as well. createMany may be able to mass insert rows and save on multiple db/server round-trips.