Sync by PivotTable ID instead of Model ID

Question

I want to update the order products by pivot ID using sync method because in my scenario an order can have multiple products with the same ID and I want to update the product whose pivot ID I am calling and delete the other ones which I have not Updated product, but you know from the sync syntax that it accepts the product's id.

Example

This is what my body and pivot table look like when I attach a product to an order

{
    "products": [
        {
            "product_id": 1,
            "color": "red",
            "quantity": 2
        },
        {
            "product_id": 1,
            "color": "black",
            "quantity": 10
        },
        {
            "product_id": 2,
            "color": "black",
            "quantity": 1
        }
    ]
}

id	order_id	Product ID	color	quantity
1	1	1	red	2
2	1	1	black	10
3	1	2	black	1

When updating order products

{
"products": [
    {
        "id" : 1,
        "product_id" :1 ,
        "color": "blue",
        "quantity": 12
    },
    
    {
        "id" : 3,
        "product_id" :2,
        "color": "blue",
        "quantity": 5
    }
]}

What do I want my table to look like

id	order_id	Product ID	color	quantity
1	1	1	blue	12
3	1	2	blue	5

But got this expected error

"message": "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' (SQL: insert into `order_product` (`color`, `created_at`, `id`, `order_id`, `product_id`, `quantity`, `size`, `updated_at`) values (blue, 2022-07-04 21:38:25, 7, 3, 1, 12, S, 2022-07-04 21:38:25))"

Order Controller

public function update(AdminUpdateOrderRequest $request, $id)
{
    $orderValidated = $request->validated();

    $order = Order::findOrFail($id);

    $order->update($orderValidated);

    if (isset($orderValidated['products'])) {
        $order->products()->sync($orderValidated['products']);
    }

    DB::table
    return OrderResource::make($order)->additional([
        'success' => true,
    ]);
}

Order product migration

Schema::create('order_product', function (Blueprint $table) {
        $table->id();
        $table->foreignId('order_id')->nullable()->constrained('orders')->onUpdate('cascade');
        $table->foreignId('product_id')->nullable()->constrained('products')->onUpdate('cascade');
        $table->integer('quantity')->nullable();
        $table->string('color')->nullable();
        $table->timestamps();
    });

Any ideas how to solve my problem?

Answer 1

You need to handle this without belonging to multiple relationships and have to create an intermediate or pivot model.

class OrderProduct extends Model {
    // optional belongs to order and product method
    protected $fillable = [
        'product_id',
        'order_id',
        'color',
        'quantity'
    ];
}

And you need to change the order and product model (if not used, the product model is optional)

class Order extends Model {
    public function order_products()
    {
        return $this->hasMany(OrderProduct::class);
    }
}

In your controller update method

// Assume your request structure is

[
    'id' => 1, // Order id,
    'order_attribute_1', // Any of your order model attribute
    'order_attribute_2', // Any of your order model attributes
    'products' => [
        [
            'id' => null, // id of the order_product table so, null cause is a new added product
            'order_id' => 1, // id of order
            'product_id' => 1// id of product
            'color' => 'blue',
            'quantity' => 12
        ],
        [
            'id' => 1, // id of the order_product table so, has id cause are you updating an existent product in the order
            'order_id' => 1, // id of order
            'product_id' => 1// id of product
            'color' => 'blue',
            'quantity' => 5
        ]
    ]
]

You need to create your own sync method

public function update(AdminUpdateOrderRequest $request, $id)
{
    $orderValidated = $request->validated();

    $order = Order::findOrFail($id);

    $order->update($orderValidated);
    
    // start simulation of sync method
    $new_order_products = [];
    // id of order_product to preserve
    $order_products_to_keep = [];

    // I hope you have the corresponding products validation on your request class
    foreach ($request['products'] as $request_product) {
        // need to create new product in order
        if (empty($request_product['id'])) {
            $new_order_products[] = [
                'product_id' => $request_product['product_id'],
                'order_id' => $request_product['order_id'],
                'color' => $request_product['color'],
                'quantity' => $request_product['quantity']
            ]
        } else {
            // find the order_order product register
            $order_product = OrderProduct::find(request_product['id']);
            // update all except the order id
            $order_product->update([
                'product_id' => $request_product['product_id'],
                'color' => $request_product['color'],
                'quantity' => $request_product['quantity']
            ]);
            // as exists this order_product will be preserved
            $order_products_to_keep[] = $order_product->id;
        }
    }

    // calculate the order_products to delete (if not present means that needs to be deleted)
    foreach ($order->order_products as $order_product) {
        if (!in_array($order_products_to_keep, $order_product->id)) {
            $order_product->delete();
        }
    }

    // mass insertion of new order_products
    $order->order_products()->createMany($new_order_products);

    DB::table
    return OrderResource::make($order)->additional([
        'success' => true,
    ]);
}

I hope this helps and you may want to use transactions to prevent errors and add corresponding validation