Laravel: Get Newest/Oldest Records from Pivot Table in BelongsToMany

Default belongsToMany relationship in Laravel works really well - you can easily attach, detach or sync records. But what if you want to order pivot records by newest or oldest? This article will show you how.

Here's how default pivot table is described.

Migration file:

class CreateProjectUserPivotTable extends Migration
{
    public function up()
    {
        Schema::create('project_user', function (Blueprint $table) {
            $table->unsignedInteger('project_id');
            $table->foreign('project_id')->references('id')->on('projects');
            $table->unsignedInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users');
        });
    }
}

And then in the model, for example, app/Project.php:

public function users()
{
    return $this->belongsToMany(User::class);
}

So, how can we add auto-increment or timestamp fields, and then be able to order newest or oldest pivot records? Pretty simple.

In migration, we add bigIncrements and timestamps fields:

Schema::create('project_user', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedInteger('project_id');
    $table->foreign('project_id')->references('id')->on('projects');
    $table->unsignedInteger('user_id');
    $table->foreign('user_id')->references('id')->on('users');
    $table->timestamps();
});

In the model, we don't need to add anything specific about id field, but for timestamps there's a special method withTimestamps():

public function users()
{
    return $this->belongsToMany(User::class)->withTimestamps();
}

And then, we can create a separate relationship, something like this:

public function newest_users()
{
    return $this->belongsToMany(User::class)->orderBy('id', 'desc');
}

In Controller for the list of project, then we may have this:

$projects = Project::with('newest_users')->get();

And then, if you want to view the project's users starting from newest, it's this:

@foreach($project->newest_users as $user)
    <span class="label">{{ $user->name }}</span>
@endforeach

To be honest, while writing this article, I realized that timestamps fields are mostly for information only, it's not reliable to order by them, because there's a big chance for synchronization happening within the same second. So my advice is to order by id field, which is auto-increment, and look at timestamps only if you need to find out when the entry was created.

More in this article: Pivot Tables and Many-to-Many Relationships