1

The Cart_product table has 4 columns:

id | cart_id | product_id | size_id | quantity

with relationship of belongsToMany for Cart, Product and Size.

User can add a product with deferent sizes but not same size, so product->id = 1 can have size->id = 1 and size->id = 2.

I want to sync product->id and size->id where only have 1 row of same product->id and size->id.

with this code, only my product->id is synced.

        $this->cart->products()->syncWithoutDetaching(
[$product->id => ['size_id' => $size->id, 'quantity' => $quantity]]
            );

As I said I need to sync product->id and size->id, I can have a product with deferent sizes:

id | cart_id | product_id | size_id | quantity
1  | 1       |1           | 2       |2
2  | 1       |1           | 3       |1

but not a product with same sizes:

id | cart_id | product_id | size_id | quantity
1  | 1       |1           | 2       |2
2  | 1       |1           | 2       |1

I have checked many cases as :

        $this->cart->products()->syncWithoutDetaching(
[$product->id, $size->id => ['quantity' => $quantity]]
            );

But it can't take true result!

Mehdi Yaghoubi
  • 561
  • 3
  • 8
  • 24

1 Answers1

1

BelongsToMany relation are made for pivot tables with only two foreign keys as their unicity index. In your case, the unicity is obtained with three indexes cart_id, product_id and size_id so you can't use any predefined method from the relation to achieve your goal.

side note: I suggest you add this constraint in your database so it triggers an error if your code tries to insert two rows with the same values in those indexes

To sync with three indexes I suggest you use updateOrInsert() or upsert()

\DB::table('your_pivot_table_name')->updateOrInsert(
    ['cart_id' => 1, 'product_id' => 1, 'size_id' => 2], //the unique index to look out for
    ['quantity' => 2] //the other fields to sync
);

Another idea would be to declare the pivot table as Custom Intermediate Table Models and add the method to "sync" using updateOrCreate() to it.

EDIT: upsert

DB::table('cart_product')->upsert(
    [
        [ 'cart_id' => $this->instance()->id, 'product_id' => $product->id, 'size_id' => $size->id, 'quantity' => (int) $qunatity]
    ], 
    ['cart_id', 'product_id', 'size_id'], 
    ['quantity']
);
N69S
  • 16,110
  • 3
  • 22
  • 36
  • thank you for the clean comment, but I get this error with both upsert and ... also I change the cart_id and so on type to unsignedBigInteger but same error: SQLSTATE[HY000]: General error: 1364 Field 'quantity' doesn't have a default value (SQL: insert into `cart_product` (`cart_id`, `product_id`, `size_id`) values (1, 2, 2) on duplicate key update `cart_id` = values(`cart_id`), `product_id` = values(`product_id`), `size_id` = values(`size_id`)) – Mehdi Yaghoubi May 11 '22 at 15:27
  • @MehdiYaghoubi use the example i provided using `updateOrCreate` or can you comment how you called `upsert()` in answer to this comment, because upsert has a third parameter on what fields should be updated (apart of the 3 indexes) and you need to provide `['quantity']` in it https://laravel.com/docs/9.x/eloquent#upserts – N69S May 11 '22 at 15:30
  • public function add(Product $product, Size $size, $qunatity = 1){DB::table('cart_product')->updateOrCreate(['cart_id' => $this->instance()->id, 'product_id' => $product->id, 'size_id' => $size->id], ['quantity' => (int) $qunatity]);}:BadMethodCallException Call to ... method Illuminate\Database\Query\Builder::updateOrCreate() public function add(Product $product, Size $size, $qunatity = 1{DB::table('cart_product')->upsert( [[ 'cart_id' => $this->instance()->id, 'product_id' => $product->id,size_id' => $size->id,]], ['quantity' => (int) $qunatity]);}:'quantity' doesn't have a default value – Mehdi Yaghoubi May 11 '22 at 16:08
  • @MehdiYaghoubi For the `updateOrCreate` was implemented in laravel 5.3, if you have an older version, it is not available. For the upsert, the third parameter has only the indexes. try this `DB::table('cart_product')->upsert( [[ 'cart_id' => $this->instance()->id, 'product_id' => $product->id,size_id' => $size->id, 'quantity' => (int) $qunatity]], ['cart_id', 'product_id', 'size_id'], ['quantity']);` added it to the answer – N69S May 11 '22 at 18:02
  • @MehdiYaghoubi there was a typo for `updateOrCreate()`, it is available only threw a model class like `Product::updateOrCreate()`, but when you use `\DB::` directly you need to use `updateOrInsert()` so try `DB::table('cart_product')->updateOrInsert(['cart_id' => $this->instance()->id, 'product_id' => $product->id, 'size_id' => $size->id], ['quantity' => (int) $qunatity]);` also edited the answer, sorry for the confusion.* – N69S May 11 '22 at 18:11