Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

ferares's avatar

Pivot table update: updateExistingPivot or syncWithoutDetaching? neither works

Hi, I'm trying to update a column on a pivot table on a many to many relationship and nothing seems to work, maybe someone here can give me a hand with it :)

I have a "carts" table:

Schema::create('carts', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
});

Then a "products" table:

Schema::create('products', function (Blueprint $table) {
      $table->id();
      $table->string('sku')->unique();
      $table->string('slug')->unique();
      $table->longText('name');
      $table->float('price');
      $table->timestamps();
  });

Finally I have a "cart_product" table:

Schema::create('cart_product', function (Blueprint $table) {
    $table->id();
    $table->index(
        ['product_id', 'cart_id', 'color', 'size'],
         'product_cart_color_size'
     );
    $table->integer('quantity')->unsigned()->default(0);
    $table->string('color')->default('');
    $table->string('size')->default('');
    $table->bigInteger('product_id')->unsigned();
    $table->bigInteger('cart_id')->unsigned();
    $table->timestamps();
});

Schema::table('cart_product', function (Blueprint $table) {
    $table->foreign('cart_id')->references('id')->on('carts')->onDelete('cascade');
    $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
});

And a relationship between the three defined in the Cart model:

// Cart model
public function products() {
    return $this->belongsToMany('App\Product')
      ->withTimeStamps()
      ->using('App\CartProduct')
      ->withPivot('quantity', 'color', 'size');
}

And in the CartProduct model:

// CartProduct model
public function product() {
    return $this->belongsTo('App\Product');
}

public function cart() {
    return $this->belongsTo('App\Cart');
}

The functionality I'm trying to implement is that users should be able to add products to the cart and if they add the same product more than once (same product, color and size) then the quantity on that cart line gets updated.

When a user adds a product to the cart, the controller handles that request with the following method:

public function add()
{
    // Get the active cart from the session
    $cart = $this->request->cart;
    // Get the SKU of the product
    $sku = $this->request->sku;
    // Get the product or 404
    $product = Product::select(['id', 'price'])->where('sku', $sku)->firstOrFail();
    // Get the size & color
    $size = $this->request->size;
    $color = $this->request->color;
    // Get the quantity to add
    $quantity = $this->request->quantity;
    // Get or create the cart line
    // If there's already a line in the cart for this product
    // with the same size & color we just need to update its quantity
    $line = CartProduct::firstOrCreate([
      'product_id' => $product->id,
      'cart_id' => $cart->id,
      'size' => $size ? $size : '',
      'color' => $color ? $color : '',
    ]);
    // Set the new quantity
    $quantity += $line->quantity;
    // .....

Everything seems to work until this point, when I actually try to persist the quantity update. (CartProduct::firstOrCreate works as expected and finds an existing line if there was one or creates a new one with its initial quantity set to 0).

I've tried 2 ways (with lots of variations for each one) of saving the quantity changes to the DB, first I tried with updateExistingPivot:

$cart->products()->updateExistingPivot($line->id, ['quantity' => $quantity]);
$cart->save();

Then with syncWithoutDetaching:

$cart->products()->syncWithoutDetaching([
    $line->id => [
          'quantity' => $quantity,
          'product_id' => $product->id,
          'cart_id' => $cart->id,
          'size' => $size ? $size : '',
          'color' => $color ? $color : '',
      ]
]);
$cart->save();

The solution using updateExistingPivot does not do anything at all.

The solution using syncWithoutDetaching actually inserts a new row into the pivot column each time, so it's not updating the quantity of a row, it just makes a duplicate and sets the quantity on that new one.

Both solutions return with no errors.

What am I missing?

0 likes
17 replies
vincent15000's avatar

Hello,

I am not sure ...

Would it be the save() method which is reponsible of the addition of a new row ?

Perhaps you should use the update() method instead ?

Tell me if I'm wrong please.

1 like
ferares's avatar

Thanks for taking the time to answer, I changed $cart->save() for $cart->update() and the results were the same :(

vincent15000's avatar

I had a look at the documentation.

I think that it is not necessary to user the save() method.

Only updateExistingPivot() or syncWithoutDetaching() method.

The save or update action seems to be implicit when using updateExistingPivot() or syncWithoutDetaching().

So I wonder your save() method adds a new row, but the existing ones which you want to update with updateExistingPivot() are already updated.

ferares's avatar

yeah I think you're right and I don't need the save/update call but I just deleted that line and the behavior is still the same for both updateExistingPivot() and syncWithoutDetaching()

ferares's avatar

Also, if I try to save the pivot table element on its own $line->save() (which I believe should have the same effect as syncing or updating existing pivot) I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' (SQL: update `cart_product` set `quantity` = 1, `cart_product`.`updated_at` = 2020-06-30 14:25:27 where `` = 1 and `` = 11) 

both cart_id and product_id column names are missing

vincent15000's avatar

That's strange that you have the same behavior after deleting the save() or update() method.

Can you write a dd($line) after your $line = ... and give me the result ?

$line = CartProduct::firstOrCreate([
      'product_id' => $product->id,
      'cart_id' => $cart->id,
      'size' => $size ? $size : '',
      'color' => $color ? $color : '',
    ]);
dd($line);
ferares's avatar

Sure, (I did \Log::info($line) instead of dd):

{
	"id":77,
	"quantity":0,
	"size":"small",
	"color":"red",
	"product_id":1,
	"cart_id":12,
	"created_at":"2020-06-30T17:06:54.000000Z",
	"updated_at":"2020-06-30T17:06:54.000000Z"
}

The timestamps do not correspond to the time at which I ran the code (17:40 UTC) so it is getting an existing row from the DB, like I said until I try to edit the quantity everything works as expected

vincent15000's avatar

And can you add a product in your cart, show the dd($line) (or your other function), then add another product in the same cart, and show the dd($line) once again ?

ferares's avatar

New product:

{
  "product_id":1,
  "cart_id":12,
  "color":"red",
  "size":"small",
  "updated_at":"2020-06-30T17:45:26.000000Z",
  "created_at":"2020-06-30T17:45:26.000000Z"
}

Same product again:

{
  "id":78,
  "cantidad":0,
  "color":"red",
  "size":"small",
  "product_id":1,
  "cart_id":12,
  "created_at":"2020-06-30T17:45:26.000000Z",
  "updated_at":"2020-06-30T17:45:26.000000Z"
}  

Other new product:

{
  "product_id":1,
  "cart_id":12,
  "color":"blue",
  "size":"small",
  "updated_at":"2020-06-30T17:45:52.000000Z",
  "created_at":"2020-06-30T17:45:52.000000Z"
}  
vincent15000's avatar

There's a problem ... with another product, you have the same product_id ... that's not normal.

ferares's avatar

Yeah I didn't express myself correctly, the product is the same but the method should create a new "line" if the color or the size are different.

Here's a different product

{
  "product_id":2,
  "cart_id":12,
  "color":"red",
  "size":"small",
  "updated_at":"2020-06-30T17:52:19.000000Z",
  "created_at":"2020-06-30T17:52:19.000000Z"
}  
vincent15000's avatar
$line = CartProduct::firstOrCreate([
      'product_id' => $product->id,
      'cart_id' => $cart->id,
      'size' => $size ? $size : '',
      'color' => $color ? $color : '',
    ]);

The documentation shows that when you are using a firstOrCreate() method, save() is not useful. firstOrCreate() creates and saves the datas. So perhaps you create a first time with firstOrCreate() and a second time with updateExistingPivot().

Your problem of duplication is perhaps with your firstOrCreate() method. Perhaps you can try to comment the updateExistingPivot() line to check wether a new line is created or not.

ferares's avatar

Yeah, firstOrCreate inserts a new row into the DB but then I need to update it's quantity, that's why I need to do something else afterwards.

ferares's avatar

I've also noticed that the object I get with firstOrCreate only has an id property if it finds a row in the DB, if the method had to add a new row then there's no id.

ferares's avatar

I'm going with this "not ideal" solution for now but I would love to understand what was wrong with my original approach and I'm willing to keep trying out suggestions of possible solutions :)

$line = CartProduct::select(['id', 'quantity'])
->where('product_id', $product->id)
->where('cart_id', $cart->id)
->where('size', $size ? $size : '')
->where('color', $color ? $color : '')
->first();

if ($line) {
    $line->quantity += $quantity;
    $line->save();
} else {
    CartProduct::create([
        'product_id' => $product->id,
        'cart_id' => $cart->id,
        'size' => $size ? $size : '',
        'color' => $color ? $color : '',
        'quantity' => $quantity,
    ]);
}
ferares's avatar
ferares
OP
Best Answer
Level 1

Well I found the problem and I feel like an idiot :V

updateExistingPivot takes, as its first argument, the id of the row on the related table, not the id of the row on the pivot table.

Final working code looks like this:

public function add()
{
    // Get the active cart from the session
    $cart = $this->request->cart;
    // Get the SKU of the product
    $sku = $this->request->sku;
    // Get the product or 404
    $product = Product::select(['id', 'price'])->where('sku', $sku)->firstOrFail();
    // Get the size & color
    $size = $this->request->size;
    $color = $this->request->color;
    // Get the quantity to add
    $quantity = $this->request->quantity;
    // Get or create the cart line
    // If there's already a line in the cart for this product
    // with the same size & color we just need to update its quantity
    $line = CartProduct::firstOrCreate([
        'product_id' => $product->id,
        'cart_id' => $cart->id,
        'size' => $size ? $size : '',
        'color' => $color ? $color : '',
    ]);
    // Set the new quantity
    $quantity += $line->quantity;
    // Save the changes
    $cart->products()->updateExistingPivot($line->product_id, ['quantity' => $quantity]);
    // Response
    return response()->json(['cart_total_quantity' => $cart->total_quantity]);
}

Thank you Vincent for all your time & help

vincent15000's avatar

No problem.

Me too I didn't care at all about the the first parameter.

Simple solution are sometimes the more difficult to find because we don't look at the good part of code.

Good luck.

1 like

Please or to participate in this conversation.