jdavidzapatab's avatar

Soft Delete pivot records

I'm working with relationships with Laravel 5.2 and Eloquent but I have not been able to find a way to soft-delete relationship records (a.k.a Pivot records). My specific case is:

I Have a Company model and I also have a Person model. I simply want to relate one or more Person records with a Company using the "employees" table. That table has some fields including the "deleted_at" field (added through a migration script).

I can get the employees() records thanks to the belongsToMany definition:

public function employees() {
        return $this->belongsToMany(Person::class, 'employees')
            ->withPivot(['start_date', 'end_date'])
            ->withTimestamps();
}

But whenever I try to detach() or even delete() one of the pivot records, I see the actual record is deleted from the database:

$employee = $companyRecord->employees()->wherePivot('person_id', $person_id)->first();
$employee->pivot->delete(); // The relationship record in 'employees' table is removed completely.

I want to soft-delete those Pivot records because I need to be able to get them back later if I want to know who worked for that company in the past, and how long did that person last in that company, and perhaps some other reference details.

I've seen the possibility to override the newPivot method on the Company and Person models, and the creation of certain Pivot class by extending the "Illuminate\Database\Eloquent\Relations\Pivot" class (see here [https://softonsofa.com/laravel-custom-pivot-model-in-eloquent/]) but that method does not seem to work for detach/delete/sync methods, only for reading operations...

Laravel documentation also lacks of information about the possibility of soft-deleting pivot records. I've seen some other examples where the belongsToMany relationship is defined in a way that it retrieves records with "deleted_at IS NULL" only, but I want to be sure that there is not a Laravel-way to do that already.

Thanks for any help.

0 likes
9 replies
rodrigo.pedra's avatar

The default Pivot Model instantiated when using a belongs to many relationship does not use the SoftDeletes trait. When using a belongs to many laravel instantiate the pivot records using the model defined in this file:

https://github.com/laravel/framework/blob/master/src/Illuminate/Database/Eloquent/Relations/Pivot.php

One option is to override the newPivot method on the Model:

https://github.com/laravel/framework/blob/master/src/Illuminate/Database/Eloquent/Model.php#L1880-L1892

to return a Model with the SoftDeletes trait applied to it.

raarts's avatar

Can you post a code example of this?

Snapey's avatar

@raarts Why would you want to soft delete the pivot entry. Better to leave it in place until one of the related models is actually deleted.

If its just a pivot then it should be irrelevant if it is deleted or not.

4 likes
beertastic's avatar

I am looking to do this and thought that I'd re-iterate the OPs request..

"I want to soft-delete those Pivot records because I need to be able to get them back later if I want to know who worked for that company in the past, and how long did that person last in that company, and perhaps some other reference details."

Makes sense to me, failing that, create another table to track history.. but we've already got the pivot table...

Anyhoo, I'll try the suggestion above..

3 likes
mmickelson's avatar

I'd like to know how this was resolved as well. The OP's request is very similar to my use case.

I have companies and products they sell along with some additional references to license table records that get created when the relationship is created. I'd like to detach a company with a product when they no longer sell that product. If they sell it again in the future, I don't want to create a new license table record. Instead, I want to use the old one.

Soft deleting the pivot record seems like a legitimate approach but I'm also having a tough time finding the "right way" to do it.

3 likes
Snapey's avatar

@mmickelson

i don't understand the relation to licences so I will use Company and Product

the relationship links the two, you want to 'soft' detatch the relationship

What I would do is create a model instead of the pivot. If you had a CompanyProduct model which belongsTo Company and belongsTo Product, then you can have deleted_at columns and treat it as a full model, with softdelete capabilities

Does it help to think about it that way?

4 likes
alsuara's avatar

@Snapey Hi. I don't know if you are still there but I have a question. I tried to do as you said (defining «Project» as a Model with Softdeletes, not a s a Pivot), but when in model «Work» I use:

public function markets(): BelongsToMany
{
    return $this->belongsToMany(Country::class, 'projects', 'work_id', 'market_id');
}

or

public function marketsWT(): BelongsToMany
{
    return $this->belongsToMany(Country::class, 'projects', 'work_id', 'market_id')->withTrashed();
}

I always obtain the same result: all related records are included, even if the projects are soft-deleted.

Can you help me?

Thanks and best regards.

raihanrazon's avatar

@alsuara You have to explicitly check deleted_at condition

public function markets(): BelongsToMany
{
    return $this->belongsToMany(Country::class, 'projects', 'work_id', 'market_id')
			->whereNull('projects.deleted_at');
}

Please or to participate in this conversation.