1

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'
    
);
Biagio Arobba
  • 1,075
  • 11
  • 27
  • How does `status 1` come into this? The matching user must have had both `status 1` **and** `status 2` set, but `status 2` must be the last one set? – miken32 Jan 26 '22 at 18:31
  • My goal is to get the latest status when only considering the provided set of values. So if the status history was: `a, b, c, d, e` and I want `latestStatus(['a', 'c', 'x', 'h'])` then the answer is `c`. I am referring to the `laravel-model-status` package found at https://github.com/spatie/laravel-model-status. In this package, there is one table that keeps a history of past status values. – Biagio Arobba Jan 27 '22 at 21:08
  • From your example, I'm not sure of what you want to query. Do you want to query all models having a current status in your set? Or do you want to query the latest status from a model, that is included in your set (this would be your example case). – Mtxz Feb 01 '22 at 12:53
  • If this was procedural for the `User` model, here is what it would look like: (1) get all status history items for all users, (2) remove any history items that are not found in `$setS` including the most current status, (3) of the remaining history items pick the latest for each user and discard the rest, (4) check that the remaining statuses are the same as status $T and discard the rest, (5) return the users represented in the final list of status history items. – Biagio Arobba Feb 02 '22 at 20:34
  • I made an edit that shows how I would do this in SQL. I might as well just write that in Eloquent. – Biagio Arobba Feb 02 '22 at 22:19

2 Answers2

0

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);
]
Mike Lucid
  • 1,344
  • 13
  • 26
  • `select(DB::raw('max(id)'))` in `scopeCurrentStatus` is not 100% true to "latest status". @biagio-arobba take a look at [their source code](https://github.com/spatie/laravel-model-status/blob/main/src/HasStatuses.php#L75]) and decide for yourself if you think this is what you want. – Thomas Van der Veen Feb 01 '22 at 18:36
  • I agree with @ThomasVanderVeen, in the `laravel-model-status` package the `latestStatus` is not the same as the `currentStatus`. My hope is that this same concept carries through as a local scope. – Biagio Arobba Feb 02 '22 at 20:40
  • I made an edit that shows how I would do this in SQL. I might as well just write that in Eloquent. – Biagio Arobba Feb 02 '22 at 21:45
0

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;

}
Biagio Arobba
  • 1,075
  • 11
  • 27