Eloquent (Laravel 5) - How do I include SoftDeleted records in relationship queries (whereHas)?

1
votes

I have built a repository layer using Eloquent. I have lots of complex relationships between tables and have been able to construct all queries easily using eloquent, I'm quite heavily reliant on WhereHas queries to query based on conditions upon relations.

With all queries done and working the final thing to do was put in an option to include softDeleted records (deleted after a certain date) within some of my queries- this has highlighted a few problems.

So for example I may have a query which starts as follows by eager loading necessary data:

public function query()
{
    $this->query = AssetInstance::with('asset.type', 'zoneInstance.type', 'zoneInstance.zone');
)

I might then have a function to optionally refine the query like so:

function filterByZoneInstance($zone_instance_id)
{
    $this->query->whereZoneInstanceId($zone_instance_id);
    return $this;
}

I might the have another function to further refine the query:

public function filterByZoneType($type)
{
    $this->query->whereHas('zone_instance', function($q) use($type){
        return $q->whereHas('type', function($q2){
            return $q2->whereName($type);
        });
    });
}

public function get()
{
     return $this->query->get();
}

So this all works fine and I could do: $this->query()->filterByZoneType('typex')->get();

Now, let's say I wanted to include softDeleteResults, I can do this:

public function includeTrashed()
{
     $this->query->withTashed();
     return $this;
}

But this does not carry through to the relationships, so yes all assetInstances (including soft-deletes will get pulled in) but not all zoneInstances which in turn causes filterByZoneType to fail if the relationship (e.g zone_instance had been soft-deleted).

So I thought no problem - I can load the relationships in with trashed:

public function query()
    {
        $this->query = AssetInstance::with(['asset.type', 'ZoneInstance' => function ($q){
                $q->withTrashed();
            }, 'zoneInstance.type', 'zoneInstance.zone']);
    )

And this kind of works until you apply a whereHas query at which point the eagerloading withTrashed gets overridden by the wherehas query which carries out it's own eager loading (without trashed);

I tried applying the constraint within the whereHas closure and it doent work:

public function filterByZoneType($type)
{
    $this->query->whereHas('zone_instance', function($q) use($type){
        return $q->whereHas('type', function($q2){
            return $q2->whereName($type)->withTrashed();
        })->withTrashed();
    });
}

I decided at this point to use a raw query unfortunately this has a similar effect, so for example if I do something like this:

$this->query->whereRaw("asset_instances.`deleted_at` <= '2014-03-11 00:00:00' and (select count(*) from `variable_data_package_instances` where `variable_data_package_instances`.`asset_instance_id` = `asset_instances`.`id` and `variable_data_package_instances`.`deleted_at` <= '2014-03-11 00:00:00')");

What I am now seeing is that trashed zoneInstances are no longer eager loaded (from query() function previously called).

Has anyone had any luck using eloquent relationship queries to bring in trashed results?

3
laraveleloquentlaravel-5soft-delete

3 Answers

3
votes

What about this:

public function query()
    {
        $this->query = AssetInstance::with(['asset.type', 'ZoneInstance' => function ($q){
                $q->withTrashed()->with('type','zone');
            }]);
    )->get();
}
1
votes

Laravel 5.2 has fixed this problem. No more need for an ugly hack.

See: https://github.com/laravel/framework/issues/11860

1
votes

I noticed the same issue with the following piece of code :

class Reward extends Model 
{
    /* ... */

    public function membership()
    {
        return $this->belongsTo(Membership::class);
    }

    /* ... */
}

And

class Membership extends Model 
{
    use SoftDeletes ;

    /* ... */

    public function rewards()
    {
        return $this->hasMany(Reward::class);
    }

    /* ... */
}

When I want to get Rewards associated to deleted Membership I use this query :

Reward::whereHas( ['membership' => function($query){
    $query->withTrashed() ;
}] )->get();

But, it doesn't work because "withTrashed" method is not a scope (adding constraints) but create a new query :

public static function withTrashed()
{
    return (new static)->newQueryWithoutScope(new SoftDeletingScope);
}

So, the only solution I found for the moment is to use an other relation :

class Reward extends Model 
{
    /* ... */

    public function membershipWithTrashed()
    {
        return $this->belongsTo(Membership::class)->withTrashed();
    }

    /* ... */
}

and then :

Reward::has( 'membershipWithTrashed' )->get();

works.

In my opinion, this is a pretty ugly solution, and SoftDelete Trait need to be fixed.