How to filter by latest status in Laravel using laravel-model-status package?

Question

CURRENT SYSTEM:
I am using Laravel and the Spatie package laravel-model-status.

NEED:
Here is a formal description of what I am looking for:

Get a list of users whose latest status in set S is the status T.

Basically, I am looking for an Eloquent example that could be turned into a local scope scopeLatestStatusEquals($T, ...$setS).

Here is how it would be used:

// Set of statuses to check for latest status:
$setS = ['status 1', 'status 2'];

// The latest status we want to filter
$T = 'status 2';

$result = MyModel::latestStatusEquals($T, $setS)->get();

BACKGROUND:
Currently, I am getting a list of all users and then filtering in a collection.

// Set of statuses to check for latest status:
$setS = ['status 1', 'status 2'];

// The latest status we want to filter
$T = 'status 2';

// The filtering using a collection:
$result = MyModel::get()->filter(function($model, $key){ 

  return $model->latestStatus($setS)->name == $T;

});

The problem is related to performance and maintainability. Ideally, this would be done using the Eloquent ORM (instead of filtering a collection) for performance, and using a scope is much cleaner.

EDIT:
Here is how I might write this in SQL:

select * 
from users u 
where u.id in (

    select s.model_id
    from statuses s
    where s.id in (

        -- Get the latest status from the set ('a', 'b', 'c')
        select max(s2.id)
        from statuses s2
        where
            s2.model_type = 'App\\Models\\User'
            and s2.name in ('a', 'b', 'c')
        group by s2.model_id
                            
    )

    -- Return only rows where the latest status is 'b'
    and s.name = 'b'
    
);

Answer 1

Here is what I came up with:

public function scopeLatestStatusEquals(EloquentBuilder $builder, $names, $valid_names = null) {

    $names = Arr::wrap($names);

    // Return models with statuses of a certain criteria
    $built = $builder
        ->whereHas('statuses', function ($query) use ($names, $valid_names) {

            // Latest statuses that match the provided $names
            $query
                ->whereIn('name', $names)
                ->whereIn('id', function($query) use ($valid_names) {

                        // Latest statuses
                        $query
                            ->selectRaw('max(s2.id)')
                            ->from('statuses as s2')
                            ->where('s2.model_type', $this->getStatusModelType());

                        if ($valid_names) {
                            $query->whereIn('s2.name', $valid_names);
                        }
                        else {
                            // pass
                        }

                        // Grouping by model ID
                        $query->groupBy('s2.'.$this->getModelKeyColumnName());


                    }
                );

        });

    return $built;

}

public function scopeLatestStatusMissing(EloquentBuilder $builder, $valid_names = null) {

    // Return models with statuses of a certain criteria
    $built = $builder
        ->whereDoesntHave('statuses', function ($query) use ($valid_names) {

            // Missing latest statuses
            $query
                ->whereIn('id', function($query) use ($valid_names) {

                        // Latest statuses
                        $query
                            ->selectRaw('max(s2.id)')
                            ->from('statuses as s2')
                            ->where('s2.model_type', $this->getStatusModelType());

                        if ($valid_names) {
                            $query->whereIn('s2.name', $valid_names);
                        }
                        else {
                            // pass
                        }

                        // Grouping by model ID
                        $query->groupBy('s2.'.$this->getModelKeyColumnName());


                    }
                );

        });

    return $built;

}

Answer 2

scopeCurrentStatus() on hasStatus will retrieve all users statuses that match $setS then bc where still on the query builder I think this would do it: (not tested)

public function scopeLatestStatusEquals($query, $T, ...$setS)
{
    $query->where(function(Builder $query) use ($setS) {
         $query->currentStatus($setS)
     })->where('name', $T);
]