Soft delete on a intermediate table for many-to-many relationship

As far as I understand it; an intermediate table is simply a length of string attaching one tables record to a record in another table and as such it does not require a soft delete method.

To explain, imagine you have a Users table and a Groups table, each user can have more than one Group and each Group can belong to more than one User. Your pivot table may be User_Group or something like that and it simply contains two columns user_id and group_id.

Your User table and Group table should have a deleted_at column for soft deletes, so when you "delete" say a Group, that group association will not appear in $User->Groups() while the pivot table row has remained unaffected. If you then restore that deleted Group, it will once again appear in $User->Groups().

The pivot table row should only be affected if that group record is hard deleted, in which case the pivot rows should also be hard deleted.

Now I have explained why I do not believe you need to add soft delete to a pivot table; is there still a reason why you need this behavior?


You can put a constraint on the Eager Load:

public function groups()
    {

        return $this
        ->belongsToMany('Group')
        ->whereNull('group_user.deleted_at') // Table `group_user` has column `deleted_at`
        ->withTimestamps(); // Table `group_user` has columns: `created_at`, `updated_at`

    }

Instead of HARD deleting the relationship using:

User::find(1)->groups()->detach();

You should use something like this to SOFT delete instead:

DB::table('group_user')
    ->where('user_id', $user_id)
    ->where('group_id', $group_id)
    ->update(array('deleted_at' => DB::raw('NOW()')));

You could also use Laravel's Eloquent BelongsToMany method updateExistingPivot.

$model->relation->updateExistingPivot($relatedId, ['deleted_at' => Carbon\Carbon::now()]);

So to use @RonaldHulshof examples you have a User model with a groups relationship which is a belongsToMany relationship.

public function groups() {
    return $this->belongsToMany(Group::class)->whereNull('groups_users.deleted_at')->withTimestamps();
}

Then in order to soft delete the pivot table entry you would do the following.

$user->groups()->updateExistingPivot($groupId, ['deleted_at' => Carbon\Carbon::now()]);