ferares
1 week ago
221
17
Eloquent

Pivot table update: updateExistingPivot or syncWithoutDetaching? neither works

Posted 1 week ago by ferares

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?

Please sign in or create an account to participate in this conversation.