Laravel Many to Many Sync with additional column

Question

Laravel version 7.0

I have Team model and User model, team_has_users table.

team_has_users table has team_id, user_id, role columns.

One user can belong to one team with different roles.

For instance, one user can belong to one team as a client and as an employee.

in Team model, I set a relation like this.

public function users(){
   return $this->belongsToMany(User::class, 'team_has_user', 'team_id', 'user_id')
       ->withPivot('role');
}

When I attach users to the team, it worked well like this.

    $item->users()->attach($request->clients, ['role'=>'client']);
    $item->users()->attach($request->employees, ['role'=>'employee']);

But, when I was going to sync them, I couldn't do.

I tried to search and found a similar one syncwithoutDetaching but it seems not to fit for my case. team_has_users table can be like this.

team_id    user_id    role
1           1         client
1           1         employee
1           2         client
1           1         other
...

Can anyone help me?

Thank you!

Takashi

Answer 1

While attach you can pass an additional array as you have passed.

$item->users()->attach($request->clients, ['role'=>'client']);
$item->users()->attach($request->employees, ['role'=>'employee']);

But In the sync you have to pass pivot value inside the array, I have mention example below.

$item->roles()->sync([1 => ['role' => 'client'], 2 => ['role' => 'employee']);

Check documentation sync part,

Laze

Thanks, can't I do $item->users()->sync($request->clients, ['role'=>'client'] ? – Takashi

Should I divide the array separately? – Takashi

No you can't. In syncing you've to assign value of pivot. and therefore you have make array. [$clientid=>['role'=>'client'],[$employeeid=>['role'=>'employee']] – Laze

This didn't work if $request->clients and $request->employees have some same ids. – Takashi

This didn't work because of team_id and user_id are same for client and employee. Otherwise as per your question this is the right solution. You have design wrong database. already user + role has relation ship and in your case you have made wrong DB design. – Laze

Then should I make the another table user_role? and need to put that table's row_id ? – Takashi

The user belongs to Team right? and user should have multiple role. as per DB design you should have three table. team, user, role..team hasMany user.. user belongsToMany role. – Laze