How can I update pivot table on laravel?

Question

I use laravel 5.3

I have 3 table : table product, table category and table products_categories

table product : id, name, etc

table category : id, name, etc

table products_categories : id, product_id, category_id

In model product, I have method this :

public function categories()
{
    return $this->belongsToMany(Category::class, 'products_categories', 'product_id', 'category_id')
                ->withPivot('id')
                ->withTimestamps();
}

So 1 product have many category

My code like this

For example $param['category'] like this :

Array ( ['category1'] => 4 ['category2'] => 11 ['category3'] => 18 )

$product_id = 1

foreach ($param['category'] as $category) {
    Product::find($product_id)
        ->categories()
        ->attach(
            $category, 
            []
        );
}

It used to add category on the pivot table and it works

But if I update category on the pivot table, it does not work

I try like this :

For example the category previously edited like this

$param['category'] =

Array ( ['category1'] => 5 ['category2'] => 12 ['category3'] => 19 )

$product_id = 1

And the code to update data on the pivot table like this :

foreach ($param['category'] as $category) {
    Product::find($product_id)
           ->categories()
           ->wherePivot('product_id', $product_id)
           ->updateExistingPivot($category, ['category_id' => $category]);
}

It did not update the field category successfully

How can I solve it?

Gabriel Caruso · Accepted Answer · 2019-11-15T15:45:29.850

5

Try to use the sync() function

Product::find($product_id)->categories()->sync($array_of_categories_id)

edited Nov 15 '19 at 15:45

answered Jun 13 '17 at 11:06

Gabriel Caruso

789
1
7
17

@Gabriel Caruso, Your answer seems correct. It seems that it also does not use loops. I will make sure again. Btw, I want to ask. How do you think my code to add data. I use a loop to add category. Does it need not use loop? – moses toh Jun 13 '17 at 15:55
@Amit Gupta, Yes, seems the answer correct. But I will make sure again. Btw, How do you think my code to add data? Is that true? I use loop to add it – moses toh Jun 13 '17 at 15:56
Loop is not always the best way. Image that your product has 1.000 categories, 1.000 loops? No! Database (like MySQL) alows you to [batch insert](https://stackoverflow.com/questions/5526917/how-to-do-a-batch-insert-in-mysql), and the `sync()` function uses this! – Gabriel Caruso Jun 13 '17 at 15:58
You don't need to use loop here. – Amit Gupta Jun 13 '17 at 16:00
@Gabriel Caruso, I am confused if not using a loop. How can I get the category? Maybe you can update your answer. So besides exist code for update category, there is also exist code to add category – moses toh Jun 13 '17 at 16:03
@Amit Gupta, I am confused if not using a loop. How can I get the category? – moses toh Jun 13 '17 at 16:03
@TrendingNews to access your categories by a product, just use the [many to many relationship](https://laravel.com/docs/5.4/eloquent-relationships#many-to-many) like `$product->categories()->get()`, and here you can loop. But, to add and update your relationship, just use the `sync()` method – Gabriel Caruso Jun 13 '17 at 16:06
1

@Gabriel Caruso, Great. I use this : `Product::find($product_id)->categories()->sync($param['category']);` to add and update category, it works. Thanks a lot – moses toh Jun 13 '17 at 16:24
@TrendingNews Glad I could help. If you can, mark this answer as the correct one, I'd appreciate that :) – Gabriel Caruso Jun 13 '17 at 16:30
@Gabriel Caruso, Okay. I will give you answer. But I want ask one last question. This is about delete category on the table pivot. I try like this : `Product::find($product_id)->categories()->where('product_id', $product_id)->delete();`. But it does not works. There exist error : `Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails`. How can I delete category correctly? – moses toh Jun 13 '17 at 16:34
@TrendingNews This is a security reason. Use the `detach()` method from the [documentation](https://laravel.com/docs/5.4/eloquent-relationships#updating-many-to-many-relationships), and should work. Ex: `Product::find($product_id)->categories()->where('product_id'‌, $product_id)->detach();` to remove all relations and then `Product::find($product_id)->categories()->where('product_id'‌, $product_id)->delete();` – Gabriel Caruso Jun 13 '17 at 16:38
@Gabriel Caruso, There exist error. This code : `->where('product_id'‌‌, $product_id)`. That's what caused the error. There seems to be a misplaced or wrong field call – moses toh Jun 13 '17 at 16:51
@TrendingNews Wow, not seen that coming. Please, chat me and I’ll try to help you! – Gabriel Caruso Jun 13 '17 at 16:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/146555/discussion-between-trending-news-and-gabriel-caruso). – moses toh Jun 13 '17 at 16:57
@Gabriel Caruso, Like this : `Product::find($id)->categories()->detach();`, it works – moses toh Jun 13 '17 at 17:37
@Gabriel Caruso, I need you help. Look at this : https://stackoverflow.com/questions/45231810/how-can-i-use-db-transaction-in-laravel – moses toh Jul 21 '17 at 07:40

How can I update pivot table on laravel?

1 Answers1

Linked