Level 63
One problem at a time.
Problem #1
$synced = $product->categories()->sync([250]);
Your code should work, so have you checked in the database if there are the right values saved in the pivot table ?
Jan 2, 2023
11
Level 1
Sync method with extra pivot data not functioning as expected
I am working on a project in laravel that uses a fair amount of both pivot tables and polymorphic pivot tables. When using the sync() method I am running into a few issues, all related to using extra pivot data. What I’m basically wondering is, is there a different method to use that handles these situations better? Am I doing something wrong here? Is this just the way it is? Do I basically need to write a custom sync method?
TL;DR
Using the sync() method with extra pivot data sometimes updates unnecessarily, polluting the return data, and in other cases it does not update or remove records that weren’t in the provided array.
Example:
I have a product with Id 1 and a category with Id 250. The category pivot table has an additional column called 'type'.
We already have this record:
category_id: 250, product_id: 1, type: ‘customer’, (plus created_at and updated_at).
And a relationship:
public function categories() { return $this->belongsToMany(Category::class)->withPivot('type')->withTimestamps(); }
And a model instance in the variable $product:
$product = Product::find(1);
Problem #1
If I do something like this:
$synced = $product->categories()->sync([250]);
$synced will be an array where attached, detached, and updated are all empty arrays. In this case, I would expect that I was sending sync off to make sure that the only relationship between product 1 and any categories is the one record with category 250 and that it would have no extra pivot data, as I didn't include any. However, sync is clearly only caring about the fact that the only category linked up with product 1 is of id 250. I could see this being the intended behavior. If it is, is there a better way to handle this? I want to actually be able to use the return data from sync. If I have to detach everything then sync it every time the return data is useless.
Problem #2
Next up, same scenario except I include pivot data that matches the existing record:
$synced = $product->categories()->sync([250 => ['type' => ‘customer’]]);
This time, $synced now has some data in updated. Instead of being empty, it says it updated the relationship with category 250.
$synced = [ 'attached' => [], 'detached' => [], 'updated' => [0 => 250], ]
It doesn't mention the extra pivot data, which I find odd. Additionally, it has updated the 'updated_at' column in the pivot table even though no other data has changed. The record that was there before already had the type filled in as ‘customer’. I asked it to sync a relationship plus pivot data. All of that already existed in the pivot table. I'd expect the result this time to be empty arrays for attached, detached, and updated.
Edit:
I removed the last set of problems to move to a separate thread to keep each more focused.
Level 1
@vincent15000 Thanks for the reply.
In short, the code does work. I have synced thousands and thousands of records. So I don't think anything is set up wrong with the relationships or anything like that. The biggest issue is that I don't know why calling sync with extra pivot data included is triggering an update when all the data is identical and doesn't actually need to be updated.
1 like
Level 63
@adam-j Have you tried syncWithoutDetaching ?
Level 24
can you make the question more shorter, this help ? ->syncWithPivotValues(...)
https://laravel.com/docs/9.x/eloquent-relationships#syncing-associations
1 like
Level 1
@newbie360 Thats a good call. I'm going to remove problems 3-3b and move them to a separate question to keep this one more focused.
As for ->syncWithPivotValues(). Unfortunately that will not work here. That sets the same extra pivot data with every id provided. So if I wanted to sync a product with category_id 250 with type "customer" and category_id 100 with type "warehouse" that would not work.
1 like
Level 19
@adam-j,
If you take a look at the syncWithPivotValues() function, you'll notice that it just passes a collection to sync(). Thus, you can update pivot values in the following form:
$synced = $product->categories()->sync([
250 => ['type' => 'customer'],
100 => ['type' => 'warehouse'],
]);
1 like
Level 1
@cwhite Thanks for the reply.
To clarify, the issue I'm having is not that I cannot get sync to function. My problem is that it's not actually doing what it sounds like it should do, make the database entries match what I provided to it.
In problem 1, the issue is that if I sync just an id it won't remove extra pivot data that I didn't include.
In problem 2 (the more serious issue), if I pass data to sync that is already in the database exactly as I provided, it still updates the records even though nothing changed and the return data also claims it had to make updates.
So if I already had a database entry for category_id 250 and type 'customer' and then I call sync passing it [250 => ['type' => 'customer']] it will update that record even though it was already correct and the return will say it updated one record for category_id 250.
1 like
Level 19
@adam-j If you want to update the pivot values for an existing row, then you should use updateExistingPivot.
After digging into the framework, it looks like it might not update the pivot row with the same data when using a custom Pivot Model (see updateExistingPivotUsingCustomClass()).
1 like
Level 19
@adam-j,
Problem #2 is a non-issue, it is just how the sync() function behaves. The $synced array only contains the ids (and not the attributes) of the pivot table, and updates/touches any synced records that were not attached or detached.
1 like
Level 1
@cwhite I'd definitely disagree that its a non-issue. But I take your point that its just the way it is.
This does not sound like the correct behavior to me. If I don't provide extra pivot data, it only touches things that need to be changed to match the data I provide. If I do include extra pivot data, it runs update statements unnecessarily, falsely updates the updated_at column, and pollutes the return data. It seems odd to me that the documentation presents sync as the way to do this exact thing but it can't actually handle a lot of situations correctly when you start including extra pivot data.
Also, just saw you reply about custom pivot models. I'll take a look into that. That sounds promising. Thanks again.
1 like
Level 19
@adam-j, if you want to remove pivot data when syncing, it's probably be best to be explicit about it:
$synced = $product->categories()->sync([250 => ['type' => null]]);
1 like
Please or to participate in this conversation.