Order by Pivot Fields

When working with belongsToMany and pivot tables, you can create extra columns in the pivot table. Then, use wherePivot() and orderByPivot() to create a separate relationship function in the Model, already filtered/ordered the way you need.

app/Models/User.php:

public function roles(): BelongsToMany
{
    return $this->belongsToMany(Role::class)
                ->withPivot('active')
                ->withTimestamps();
}
 
public function latestActiveRoles(): BelongsToMany
{
    return $this->roles()
                ->wherePivot('active', 1)
                ->orderByPivot('created_at', 'desc');
}

Some Controller:

$users = User::with('latestActiveRoles')->get();