Preventing Laravel adding multiple records to a pivot table

Question

I have a many to many relationship set up and working, to add an item to the cart I use:

$cart->items()->attach($item);

Which adds an item to the pivot table (as it should), but if the user clicks on the link again to add an item they have already added it creates a duplicate entry in the pivot table.

Is there a built in way to add a record to a pivot table only if one does not already exist?

If not, how can I check the pivot table to find if a matching record already exists?

Barryvdh · Answer 1 · 2016-08-19T17:23:42.953

314

You can also use the $model->sync(array $ids, $detaching = true) method and disable detaching (the second param).

$cart->items()->sync([$item->id], false);

Update: Since Laravel 5.3 or 5.2.44, you can also call syncWithoutDetaching:

$cart->items()->syncWithoutDetaching([$item->id]);

Which does exactly the same, but more readable :)

edited Aug 19 '16 at 17:23

answered Apr 05 '14 at 19:11

Barryvdh

6,419
2
26
23

2

The second boolean parameter to prevent detaching unlisted IDs is not in the main L5 documentation. It is very good to know - seems to be the only way to "attach if not already attached" in a single statement. – Jason Apr 12 '16 at 11:57
12

This should be accepted as the answer, it is a much better way to do it than the accepted answer – Daniel May 09 '16 at 21:25
5

For newbies like me: `$cart->items()->sync([1, 2, 3])` will construct many-to-many relationships to the id's in the given array, `1`, `2`, and `3`, and delete (or "detach") all other id's not in the array. This way, only the id's given in the array will exist in the table. Brilliant @Barryvdh uses an undocumented second parameter to disable that detaching, so no relationships not in the given array are deleted but only unique id's will be attached. [Check out the "syncing for convienence" doc. (Laravel 5.2)](https://laravel.com/docs/5.1/eloquent-relationships#many-to-many) – identicon Aug 02 '16 at 19:04
4

FYI, I updated the docs, so 5.2 and above: https://laravel.com/docs/5.2/eloquent-relationships#inserting-many-to-many-relationships And Taylor immediately added add `syncWithoutDetaching()`, which calls the sync() with false as second param. – Barryvdh Aug 19 '16 at 17:21
Laravel 5.5 https://laravel.com/docs/5.5/eloquent-relationships#updating-many-to-many-relationships `syncWithoutDetaching()` worked! – Josh LaMar Sep 26 '17 at 14:26
`syncWithoutDetaching` is nice, though naming is a bit odd to me. Would be better if it was named `attachOnce()` or something. – Gary Green Dec 12 '17 at 15:48
Laravel 7.x sync(array[], false); worked not syncWithoutDetaching – Artin GH Jul 06 '20 at 19:58
You may also use `$cart->items()->syncWithoutDetaching($item);` – Adam Dec 03 '20 at 18:05

Alexandre Butynski · Accepted Answer · 2022-01-08T10:45:34.280

80

You can check the presence of an existing record by writing a very simple condition like this one :

if (! $cart->items->contains($newItem->id)) {
    $cart->items()->save($newItem);
}

Or/and you can add unicity condition in your database, it would throw an exception during an attempt of saving a doublet.

You should also take a look at the more straightforward answer from Barryvdh.

edited Jan 08 '22 at 10:45

answered Jul 04 '13 at 18:35

Alexandre Butynski

6,625
2
31
44

1

The $id parameter for the method `attach()` is mixed, it can be an int or instance of model ;) - see https://github.com/laravel/framework/blob/master/src/Illuminate/Database/Eloquent/Relations/BelongsToMany.php?source=cc#L589 – Rob Gordijn Jul 05 '13 at 08:08
Thank you @RobGordijn. I have learned something today ! Answer edited. – Alexandre Butynski Jul 05 '13 at 08:25
What's the purpose of 'contains' statement ? It could not find any duplications for me so that I still got duplication error when I use this. – Bagusflyer May 19 '14 at 01:42
1

@bagusflyer `contains` statement check if the key is present in one object in the collection. You should have a mistake in your code. – Alexandre Butynski May 19 '14 at 09:23
7

I don't like this solution because it forces an extra query ($cart->items) I've been doing something like: `$cart->items()->where('foreign_key', $foreignKey)->count()` Which, well, actually performs an additional query too '^^ But I don't need to fetch and hydrate the whole collection unless I really need it. – Duilio May 24 '15 at 13:57
3

That's right, your solution is a bit more optimized. You can even use the `exists()` function instead of `count()` for the best optimization. – Alexandre Butynski May 26 '15 at 13:25
1

and what about when you're removing some relations? this just adds new relations – Alireza Sep 22 '16 at 15:56
Good solution if you want to pass additional intermediate table values – Hariadi Oct 09 '17 at 15:58

score 2 · Answer 3 · answered Feb 25 '14 at 15:45

2

@alexandre Butynsky method works very well but use two sql queries.

One to check if cart contains the item and one to save.

To use only one query use this:

try {
    $cart->items()->save($newItem);
}
catch(\Exception $e) {}

answered Feb 25 '14 at 15:45

Octavian Ruda

87
4

This is what I did in my project. But the problem is you don't know if this exception is caused by the duplicated entry or anything else. – Bagusflyer May 19 '14 at 01:32
2

why would it throw any exception? it would be if there's some unique key – Seiji Manoan Aug 12 '15 at 19:28

adeguk Loggcity · Answer 4 · 2017-10-06T01:32:51.963

As good as all this answers are because I had tried them all, one thing is still left unanswer or not taken care of: the issue of updating a previously checked value (unchecked the checked box[es]). I do have something similar to the above question expect i want to check and uncheck features of products in my product-feature table (the pivot table). I am a newbie and I have realised none of the above did that. The are both good when adding new features but not when i want to remove existing features (i.e. uncheck it)

I will appreciate any enlightenment in to this.

$features = $request->get('features');

if (isset($features) && Count($features)>0){
    foreach ($features as $feature_id){
        $feature = Feature::whereId($feature_id)->first();
        $product->updateFeatures($feature);
    }
}

//product.php (extract)
public function updateFeatures($feature) {
        return $this->features()->sync($feature, false);
}

or

public function updateFeatures($feature) {
   if (! $this->features->contains($features))
        return $this->features()->attach($feature);
}
//where my attach() is:
public function addFeatures($feature) {
        return $this->features()->attach($feature);
}

Sorry guys, not sure is I should delete the question because having figure out the answer myself, it sounds a bit stupid, well the answer to the above is as simple as working @Barryvdh sync() as follows; having read more and more about:

$features = $request->get('features');
if (isset($features) && Count($features)>0){
    $product->features()->sync($features);
}

score 1 · Answer 5 · answered Nov 26 '18 at 22:33

There are some great answers posted already. I wanted to throw this one out here as well though.

The answers from @AlexandreButynski and @Barryvdh are more readable than my suggestion, what this answer adds is some efficiency.

It retrieves only the entries for the current combination (actually only the id) and it than attaches it if it does not exist. The sync method (even without detaching) retrieves all currently attached ids. For smaller sets with little iterations this will hardly be a difference, ... you get my point.

Anyway, it is definitely not as readable, but it does the trick.

if (is_null($book->authors()->find($author->getKey(), [$author->getQualifiedKeyName()])))
    $book->authors()->attach($author);

This is much more performant when working with large relationships. Our server was running out of memory when using the other methods — Jeff Davis, Sep 02 '21 at 21:25

score 1 · Answer 6 · answered Oct 25 '22 at 09:29

I just had this problem and was able to solve it.

For future readers: instead of using atach() you can use

syncWithoutDetaching()

This will make sure you do not get any duplicates!

There are more alternatives to attach()

That might be usefull in the laravel documentation

Note that this is not for Laravel 4

score 0 · Answer 7 · edited Oct 16 '22 at 13:27

0

$branch->permissions()->syncWithoutDetaching([1,2,3]);

edited Oct 16 '22 at 13:27

James Risner

5,451
11
25
47

answered Oct 11 '22 at 18:00

Araz Ibrahim

21
2

Preventing Laravel adding multiple records to a pivot table

7 Answers7

Linked