Laravel. How to find models which has specific latest related model

Question

Laravel 5.3.

A Package Model has many Step Models. The Step Model has a column calls status whose type is tiny int and created_at column. For example, a package A, has these steps:

• Oct. 18 10:00, status 1
• Oct. 19 09:00, status 2

And, a package B, has these steps:

• Oct. 19 08:00, status 1
• Oct. 19 09:00, status 2
• Oct. 19 10:00, status 3

Just like that, many packages, each of them has many steps.

A's latest step's status is 2 and B's is 3

My problem is, how to find Packages whose latest step status is 2? The expected result is a collection of packages, in this example, should contains A.

I have tried add this in my Package Model.

public function steps()
{
    return $this->hasMany('App\Step');
}

public function status()
{
    return $this->steps()->latest()->limit(1);
}

and query with

Package::whereHas('status', function ($q) {
    $q->where('status', 2);
})->get();

but can't get expected result.

What not expected is, if Packages Table has only 1 row, the Package B, expected result is an empty collection. But it return a collection contains B.

I have also tried update the status function in Package Model to

public function status()
{
    return $this->hasOne('App\Step')->latest();
}

but it still not work.

So, what is the right way? Huge thank to you.

Answer 1

This is quite challenging. I couldn't think of a solution that you can achieve what you want by doing a single Eloquent query. However I found a way that gives you the result in two steps - i.e. query + filter.

But before that you need to add a scope to your Package model:

public function scopeOfStatus($query, $status)
{
    return $query->whereHas('steps', function($q) use ($status) 
    {
        $q->where('status', $status);   
    });
}

Step 1: Write a query that retrieves all packages with at least one step matching your given status:

$status = 1;
$packages = Package::with('steps')->ofStatus($status)->get();

Step 2: Filter the result to get only packages which their last step matches your given status:

$packages = $packages->filter(function($package) use ($status)
{
    return $package->steps->last()->status == $status;
});

The final result is a collection of all packages that their last step has status == 1