Order Status History and Querying Latest Status

In this lesson, we will discuss the history of statuses in some kind of record, like Order Status: "in progress", "completed", etc. What is a better way to structure it?

DB Structure

Typically, I see something like this in other people's databases. There is a statuses database table with some statuses.

Schema::create('statuses', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

Then you have an orders table with fields for the orders.

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->string('customer_name');
    $table->timestamps();
});

Then, there's a pivot table with the history of which status became active at which time.

Schema::create('order_status', function (Blueprint $table) {
    $table->foreignId('order_id')->constrained();
    $table->foreignId('status_id')->constrained();
    $table->timestamps();
});

So, the Order Model has many relationship to the Status Model with timestamps.

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Order extends Model
{
    use HasFactory;
 
    protected $fillable = [
        'customer_name',
    ];
 
    public function statuses(): BelongsToMany
    {
        return $this->belongsToMany(Status::class)->latest()->withTimestamps();
    }
}

Then, in the Controller, we get the latest orders with statuses.

use App\Models\Order;
 
class HomeController extends Controller
{
    public function __invoke()
    {
        $orders = Order::with('statuses')
            ->latest()
            ->take(10)
            ->get();
 
        return view('orders', compact('orders'));
    }
}

And show them in View.

<ul>
    @foreach($orders as $order)
        <li>
            <strong>{{ $order->customer_name }}</strong>
            <i>status {{ strtoupper($order->statuses->sortByDesc('pivot.created_at')->first()->name ?? '') }}</i>
            <ul>
                @foreach($order->statuses as $status)
                    <li>{{ $status->pivot->created_at }}: {{ $status->name }}</li>
                @endforeach
            </ul>
        </li>
    @endforeach
</ul>

In the browser, we see a similar result:

Showing Latest Status

Now, often, you might need to show only the latest status. There is more than one way to do that.

Option 1

You may use the same statuses relationship, which gives a collection. Then, use the sortByDesc() method to sort by the pivots table created_at field, get the first record, and show the name field.

<ul>
    @foreach($orders as $order)
        <li>
            <strong>{{ $order->customer_name }}</strong>
            <i>status {{ strtoupper($order->statuses->sortByDesc('pivot.created_at')->first()->name ?? '') }}</i> 
            <ul>
                @foreach($order->statuses as $status)
                    <li>{{ $status->pivot->created_at }}: {{ $status->name }}</li>
                @endforeach
            </ul>
        </li>
    @endforeach
</ul>

However, if you don't need to show a list of all the statuses using this method, you still load all the belongs-to-many relationships, which, in this case, isn't needed. We need only the last record, which means we load too much data.

Option 2

The second option is to save the latest status ID to the orders table.

Schema::table('orders', function (Blueprint $table) {
    $table->foreignId('status_id')->nullable()->constrained();
});

Then, in the Model, we add the status_id to the fillable and add a belong-to relationship to the statuses.

app/Models/Order.php:

use Illuminate\Database\Eloquent\Relations\BelongsTo;
 
class Order extends Model
{
    use HasFactory;
 
    protected $fillable = [
        'customer_name',
        'status_id', 
    ];
 
    public function statuses(): BelongsToMany
    {
        return $this->belongsToMany(Status::class)->latest()->withTimestamps();
    }
 
    public function currentStatus(): BelongsTo 
    {
        return $this->belongsTo(Status::class, 'status_id');
    }
}

Then, instead of statuses in the Controller, we load the currentStatus.

class HomeController extends Controller
{
    public function __invoke()
    {
        $orders = Order::with('statuses') 
        $orders = Order::with('currentStatus') 
            ->latest()
            ->take(10)
            ->get();
 
        return view('orders', compact('orders'));
    }
}

And in the View, we call the status name from the currentStatus relationship.

@foreach($orders as $order)
    <li>
        <strong>{{ $order->customer_name }}</strong>
        <i>status {{ strtoupper($order->statuses->sortByDesc('pivot.created_at')->first()->name ?? '') }}</i> 
        <i>status {{ $order->currentStatus->name ?? '' }}</i> 
    </li>
@endforeach

The only thing you need to do is have an observer or set the status ID to the latest one whenever the order status is applied.

Option 3

And the last third option is to make a subquery.

class HomeController extends Controller
{
    public function __invoke()
    {
        $orders = Order::addSelect([
            'currentStatus' => Status::select('name')
                ->join('order_status', 'statuses.id', '=', 'order_status.status_id')
                ->whereColumn('orders.id', '=', 'order_status.order_id')
                ->latest('order_status.created_at')
                ->limit(1)
        ])
            ->latest()
            ->take(10)
            ->get();
 
        return view('orders', compact('orders'));
    }
}

Also, the subquery can be moved to a scope.

use Illuminate\Database\Eloquent\Builder;
 
class Order extends Model
{
    use HasFactory;
 
    protected $fillable = [
        'customer_name',
        'status_id',
    ];
 
    public function statuses(): BelongsToMany
    {
        return $this->belongsToMany(Status::class)->latest()->withTimestamps();
    }
 
    public function scopeCurrentStatus(Builder $query): Builder 
    {
        return $query->addSelect([
            'currentStatus' => Status::select('name')
                ->join('order_status', 'statuses.id', '=', 'order_status.status_id')
                ->whereColumn('orders.id', '=', 'order_status.order_id')
                ->latest('order_status.created_at')
                ->limit(1)
        ]);
    } 
}

Now, the current status name in the View can be called as if it was a regular DB column.

<ul>
    @foreach($orders as $order)
        <li>
            <strong>{{ $order->customer_name }}</strong>
            <i>status {{ $order->currentStatus->name }}</i> 
            <i>status {{ $order->currentStatus }}</i> 
        </li>
    @endforeach
</ul>