Updating a pivot table

I've got a many to many relationship between a student and an institution_contact.

students should only ever have two institution_contacts and I have a attribute on the pivot table named type to be set as 1 or 2.

So, my pivot table looks like this: institution_contact_student: id, institution_contact_id, student_id, type

I've run into difficulty in deciding how to approach the issue of adding/updating the pivot table. Let's say I have 100 students and I want to assign them a contact with the type of 1.

My current solution is to delete the contact then add it:

$students = Student::all(); // the 100 students
$contactId = InstitutionContact::first()->id; // the contact

foreach ($students as $student) {
            // remove existing contact
            $student->institutionContacts()->newPivotStatement()->where('type', 1)->delete();

            // add new contact
            $student->institutionContacts()->attach([$contactId => ['type' => 1]]);
        }

However, I'm thinking that this is going to hit the database twice for each student, right? So would I be better off creating a model for the pivot table and removing all entries that matched the student id and the type then simply adding the new ones? Or would creating a model for the pivot table be considered bad practice and is there a better way of accomplishing this that I've missed?

Please note the reason I'm not using sync is because I'm relying on the type attribute to maintain only two contacts per student. I'm not aware of a way to modify an existing pivot without causing issues to my two contacts per student requirement. Also, updateExistingPivot

Any help would be appreciated!

niells

10 years ago

Level 2

Both solutions should work - if you want to update the pivot table you can do the following:

$pivot = $student->institutionContacts() ->withPivot(['id', 'institution_contact_id', 'student_id', 'type']) ->wherePivot('type',1) ->first()->pivot;

$attributes = ['institution_contact_id' => $newId] // put whatever attributes you want to update (i'm guessing you want to update institution_contact_id

$student->institutionContacts()->newPivotStatementForId($pivot->id)->update($attributes);

This will also hit the Database twice:

select on the related Model table joined with the pivot table for the desired condition: (student_id = ? and type=1 limit1)
update the pivot record attributes for the requested pivot id

If you want to detach/attach each time you would want to perform an update make sure to roll both operations inside a transaction.

haakym

10 years ago

Level 8

@niells

Thanks so much for your reply, I appreciate the example of using the withPivot function.

As an alternative, do you think it's worth running a DB query to perform the delete then I can insert the records using attach. So if I had 100 students to run through it would be 1 for the delete and 50 for the insert. Cut down by half pretty much.

DB::table('institution_contact_student') // the pivot table
    ->whereIn('student_id', $studentIds)
    ->where('type', 1)
    ->delete();

niells

10 years ago

Level 2

I'd go for the update method in this case, it makes for sense in my opinion. The delete/add method returns the same result as well, but it requires 3 operations (select, delete, update), so in this case you need to update, not delete/add.

PS: you would still require to make 100 inserts if you have 100 students, not 50, or maybe i misunderstood your requirements.

PS: I personally don't like to make use of Facades in my Models.

niells

10 years ago

Level 2

I just realized i said something which will lead to errors:

$pivot = $student->institutionContacts() ->withPivot(['id', 'institution_contact_id', 'student_id', 'type']) ->wherePivot('type',1) ->first()->pivot;

if you go for update method, $student->institutionContacts()->newPivotStatementForId($pivot->id)->update($attributes);

should be

$student->institutionContacts()->newPivotStatement()->where('id', $pivot->id)->update($attributes); this way you will use the primary key of the pivot table for the update query

And thats because newPivotStatementForId($id) reffers to the $otherKey property of the belongsToMany class. (which in your case would refer to institution_contact_id property).

my bad.

haakym

10 years ago

Level 8

@niells Thanks again for your responses!

PS: you would still require to make 100 inserts if you have 100 students, not 50, or maybe i misunderstood your requirements.

Yes you're right, sorry I was getting my calculations wrong (long day!)

In the end I decided to go for the delete using the DB facade then attaching the new items. My code looks something like this now:

// delete
\DB::table('institution_contact_student') // the pivot table
            ->whereIn('student_id', $studentIds)
            ->where('type', $contactType)
            ->delete();

// add
foreach ($students as $student) {
    $student->institutionContacts()->attach([$contactId => ['type' => $contactType]]);
 }

Please or to participate in this conversation.