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