How do I update this specific column in my many to many pivot table?

I have 2 tables in many to many relationship: carts and products

Cart.php

class Cart extends Model
{
    use HasFactory;

    //A cart can have many products
    public function product(): BelongsToMany 
    {
        return $this->belongsToMany(Product::class)->withPivot("size_id", "quantity");
    }
}

Product.php

class Product extends Model
{
    use HasFactory;

    //A product can be added to many carts
    public function cart(): BelongsToMany 
    {
        return $this->belongsToMany(Cart::class)->withPivot("size_id", "quantity");
    }

}

This is my pivot table schema

Schema::create('cart_product', function (Blueprint $table) {
   $table->foreignId("cart_id")->constrained();
   $table->foreignId("product_id")->constrained();
   $table->foreignId("size_id")->constrained();
   $table->integer("quantity");
   $table->timestamps();
});

The size_id is an extra foreign key which is not defined to be included in any relationship. I want to increment the quantity of a specific row considering all 3 attributes (cart_id, product_id and size_id). How can I go about doing this in laravel?

My attempt:

$latest_cart = $this->get_latest_cart(Auth::user()->id);
$cart_id = $latest_cart->value("id");
$cart = Cart::find($cart_id);

$item_exists = $cart->product()->where("product_id", $product_id)->where("size_id", $size_id)->exists();

if ($item_exists) {
     $quantity = $cart->product()->where("product_id", $product_id)->where("size_id", $size_id)->first()->pivot->quantity;

     //this doesn't work
     $cart->product()->where("product_id", $product_id)->where("size_id", $size_id)
        ->updateExistingPivot($product_id, ["quantity" => $quantity + 1]);  

} else {
      $cart->product()->attach($product_id, ["size_id" => $size_id, "quantity" => 1]);

}

The updateExistingPivot updates the quantity considering only 2 fields (cart_id and product_id) and therefore increments all the rows containing the particular cart_id and product_id.

mani273

2 years ago

Level 1

Due to the lack of replies, I have decided to use the DB facade to update the data as of now but I feel like it is not a clean implementation. If anyone can figure out how to do this pls drop a reply :)

Also my created_at and updated_at are not entered automatically when I attach new records, which means I am already doing something wrong here in my attempt :(

Should I define a cart belongsToMany Size and vice versa?? Idk how laravel handles or how to even implement a tertiary many to many relationship!

tangtang

2 years ago

Best Answer

Level 6

@mani273

you can try this method for pivot table

$latest_cart = $this->get_latest_cart(Auth::user()->id);
$cart_id = $latest_cart->value("id");
$cart = Cart::find($cart_id);

$item = $cart->product()
    ->wherePivot('product_id', $product_id)
    ->wherePivot('size_id', $size_id)
    ->first();

if ($item) {
    $quantity = $item->pivot->quantity;

	// update code
    $cart->product()
        ->wherePivot('product_id', $product_id)
        ->wherePivot('size_id', $size_id)
        ->updateExistingPivot($item, ["quantity" => $quantity + 1]);
} else {
    $cart->product()->attach($product_id, ["size_id" => $size_id, "quantity" => 1]);
}

1 like

mani273

2 years ago

Level 1

@tangtang Tnx your code works. So $cart->product()->where() just checks for matching values in products table and wherePivot() checks for matching values in pivot table?

tangtang

2 years ago

Level 6

@mani273

yes, you can use it to specify conditions on the pivot table when working with many-to-many relationships. it allows you to filter records based on the attributes stored in the pivot table.

1 like

Please or to participate in this conversation.