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

Question

How do I set soft delete on an intermediate table which is connecting two different types of entities? I've added deleted_at column, but the docs say that I need to put this into the model:

protected $softDelete = true;

Of course, I don't have a model for an intermediate table. Any idea?

Answer 1

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?

Answer 2

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()]);

Answer 3

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()')));