sync by pivot table id instead of model id-CodePudding

I want to update order products by pivot id with sync method cause in my scenario order can have more than a product of the same id and I want to update products I called their pivot id and delete others that I didn't update, but u know from sync syntax that it accepts ids of products.

Example

That's how my body and pivot table look like when attaching products to 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 the order products

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

how I want my table 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))"

OrderController

   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 to solve my issue?

CodePudding user response：

You need to handle this without belongs to many relation, and you must 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 need to change the order and product model (product model optional if you dont use)

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

In your controller update method

// Assuming that 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 you, and probably you need to use a transaction for preventing errors, and, add the respective validations