I have a standard relationship where Users can have multiple Roles (and Roles can have many Users). The pivot table for the relationship is role_user.
role_user includes pivot fields that scope the role. These columns are project_id and company_id and in the migration I set the primary key for the table as $table->primary(['company_id', 'project_id', 'role_id', 'user_id'], 'xxxxx');.
I need to be able to have multiple instances of the same role for a given user, with the scoping fields provising the further distinction. Eg:
| user_id | role_id | project_id | company_id |
|---------|---------|------------|------------|
| 1 | 2 | 10 | 0 |
| 1 | 2 | 11 | 0 |
| 1 | 3 | 0 | 1 |
How do I essentially "upsert" in this instance. If I want to attach a role_id=2 to user_id=1 with say project_id=20 now, how would I do that? If I do the following, and it already exists then I would get an integrity constraint violation:
User::find(1)->roles()->attach(2, ['project_id' => 20])
Do I have to manually check myself, or is there a way I can use sync? When I use sync it seems to regard only the user_id and role_id in determining uniqueness, despite my primary key.
I'm not really sure what I expected from something like User::find(1)->roles()->sync([1 => ['project_id' => 2]) but I din't expect that to overwrite row 1 in my example table above as it seems to.
Any tips, much appreciated?
