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...