Updating Material Variant IDs for Product Variants in Laravel: Distinct Mapping Issue

I’m working on a Laravel application where I need to update the material_variant_id for product variants based on a provided list of materials. I have two tables:

test_product_variant: Contains product variants with a product_id.

Columns: id, product_id, …
test_product_material_variant: Contains material variants linked to the product variants.

Columns: product_variant_id, material_id, material_variant_id
Problem:
I need to update the material_variant_id in test_product_material_variant for the same material_id but assign different material_variant_ids from an array of materials. The code I’m using currently just updates the same material_variant_id for all product variants instead of assigning distinct IDs from the materials array.

 $product_id = 1;
    $productVariants = DB::table('test_product_variant')
        ->where('product_id', $product_id)
        ->get();
    $materials = [
        [
            "material_id" => "20",
            "material_variant_id" => "4",
        ],
        [
            "material_id" => "20",
            "material_variant_id" => "8",
        ],
    ];
    $materialIndex = 0;
    foreach ($productVariants as $productVariant) {
        $product_variant_id = $productVariant->id;
        $material = $materials[$materialIndex % count($materials)];
        $material_id = $material['material_id'];
        $material_variant_id = $material['material_variant_id'];

        DB::table('test_product_material_variant')
            ->where('product_variant_id', $product_variant_id)
            ->where('material_id', $material_id)
            ->update(['material_variant_id' => $material_variant_id]);

        $materialIndex++; // increment the material index
    }

I have db structure:
test_product_variant table have columns

id

product_id

test_product_material

product_id

material_id

material_variant_id

test_product_material_variant

product_variant_id

material_id

material_variant_id

You need to sign in to view this answers