Courses

Structuring Databases in Laravel 11

Order Status History and Querying Latest Status

Summary of this lesson:
- Managing status history
- Implementing status relationships
- Tracking status changes
- Optimizing status queries

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

The full lesson is only for Premium Members.
Want to access all 18 lessons of this course? (81 min read)

You also get:

  • 69 courses (majority in latest Laravel 11)
  • Premium tutorials
  • Access to repositories
  • Private Discord