Yosua Lijanto Binar Yosua Lijanto Binar - 23 days ago 16
MySQL Question

Laravel Update Many-to-Many Foreign Key Record

I have

User
,
Role
, and
User_Role
table with many-to-many relationship between User and Role table.

class User extends Model {
public function roles {
return $this->belongsToMany('App\Role');
}
}

class Role extends Model {
public function users {
return $this->belongsToMany('App\User');
}
}

User
- id
- other_columns

Role
- id
- other_columns

User_Role
- id
- user_id
- role_id
- other_columns


User_Role
can have multiple same record with different
Role_User.id
.


How to update
User
's role without changing or deleting record of
User_Role
table? Or in other word how to update
User_Role.role_id
without changing or deleting
User_Role.id
?

I have tried:

$user->roles()->updateExistingPivot($userRoleId, ['role_id' => $newRoleId]);


and

$user->roles()->sync($userRoleId, ['id' => $userRoleId, 'role_id' => $newRoleId, 'user_id' => $userId]);


But they don't work.

Update

This code is work, but it also changes same other record.

$user->roles()->updateExistingPivot($oldRoleId, ['role_id' => $newRoleId]);


And I have tried to filter first

$user->roles()->wherePivot('id', $userRoleId)->updateExistingPivot($oldRoleId, ['role_id' => $newRoleId]);

Answer

You can try updateExistingPivot()

$user->roles()->updateExistingPivot($roleId, ['role_id' => $newRoleId]);

If you're using more than one pair of exactly the same user_id and role_id and you want to update only one row, try to use newPivotStatement():

$user->roles()->newPivotStatement()->where('id', $userRoleId)->update(['role_id' => $newRoleId]);

And solution by @Yosua Lijanto Binar:

$user->roles()->wherePivot('id', $userRoleId)->updateExistingPivot($oldRoleId, ['role_id' => $newRoleId]);