Save relationship with multiple columns

Question

I have an Laravel application and I am migrating the old database to a new database. I have made some changes in my tables, added some new tables and I deleted some tables. I have made a migration script for this.

I've a user. This user is assigned to a school and to a group. In my old database the user was assigned to one school and one group. So, I had a column in my users table named 'group_id' and a column named 'school_id'.

Now, I made a mapping table (I guess it is the right English word for this), because of the user should be assigned to multiple schools and multiple groups.

Users table:

ID | username | password | more...

Schools table:

ID | name | address | more...

Groups table:

ID | name | alias | more...

group_user table:

ID | group_id | user_id | school_user_id

user_school table:
ID | user_id | school_id

As you can see, I also added also the ID of the school_user relation to the mapping table, because of that makes it easier to work with in my application. If I remove a user from a school, I can with a foreign key remove the group_user relation. The reason is simple: a user have to assigned to a school, otherwise he can not be in a group.

My Laravel controller for saving an user is:

$user = new User;

$user->id = $gebruiker->id;
$user->username = $gebruiker->gebruikersnaam;
$user->password_md5 = $gebruiker->wachtwoord;
$user->firstname = $gebruiker->voornaam;
$user->prefix = $gebruiker->tussenvoegsel;
$user->lastname = $gebruiker->achternaam;
$user->phonenumber = $gebruiker->telefoonnummer;
$user->emailaddress = $gebruiker->email;
$user->gender = ($gebruiker->geslacht == 'man' ? 'male' : 'female'); 
$user->birthdate = $gebruiker->geboortedatum;

$user->save();

$user->groups()->sync(array('group_id' => $gebruiker->groep_id));
$user->schools()->sync(array($gebruiker->school_id));

My user model has two methods:

public function schools()
{
    return $this->belongsToMany('School');
}

public function groups()
{
    return $this->belongsToMany('Group');
}

I can save the relationship for schools. It is easy because of the mapping table school_user has two columns. Laravel does the trick for me. But, in the group_user table I've three columns

I tried to make my controller for saving the relationship like this:

 $user->groups()->sync(array('group_id' => $gebruiker->groep_id, 'school_id' => $gebruiker->school_id));

But, unfortunately, it do not work. Can anyone tell me what to do?

EDIT: Excuse me! I did not ask the right question. I have made an edit to this post, which I have made italic.

Answer 1

You need to specify any additional columns in your pivot table when you define the relation. So:

public function groups()
{
    return $this->belongsToMany('Group')->withPivot(['id','school_user_id']);
}

See the docs here.

---

Update:

You should be using attach and detach, rather than sync, to associated your users with groups and schools. sync will delete any models from the intermediary pivot table which are not in the array you pass to it—meaning in your existing code, $user->schools()->sync(array($gebruiker->school_id)) will delete all other schools that the user is associated with. This would allow a user to only be associated with one school. The same goes for groups, if you were to use sync for groups.

So after you save a user with $user->save(), you would run:

$user->schools()->attach($gebruiker->school_id);

Then, get the id of the entry that was just added to the school_user pivot table:

$school_user_id = $user->schools()->wherePivot('school_id', $gebruiker->school_id)->first()->pivot->id;

Finally, attach the user and group, and set the school_user id so you can use it as a foreign key to allow for cascading delete:

$user->groups()->attach($gebruiker->groep_id, array('school_user_id' => $school_user_id));

---

Update 2: It turns out that you can use sync rather than attach to update the pivot table when you save a user (and, in fact, should), so long as you set the 2nd attribute of sync to false. Doing so disables the detaching behavior of sync, so it will only add a new school_user connection for the user, but will not delete any of their other school connections.

$user->schools()->sync([$gebruiker->school_id], false);

The benefit of doing this is that it permits users to be connected to multiple schools, but prevents duplicate entries in the pivot table for the same school_user combination. Normally, this kind of integrity check isn't a big issue for a pivot table, since detach would clean up any duplicate entries. But since you'll be making a relationship between your pivot table and your group_user table, and relying on cascading deletes with your foreign key, it would be a good idea to make sure your pivot table stays as clean as possible.