Advanced BelongsToMany with Extra Pivot Table Features

Let's talk about a belongs-to-many, also called many-to-many relationships, but on a deeper level. What are other options besides the foreign keys columns in the pivot table?

This is a typical pivot table between a project and a user. A project may belong to many users.

Schema::create('project_user', function (Blueprint $table) {
    $table->foreignId('project_id')->constrained();
    $table->foreignId('user_id')->constrained();
});

To show users for each project, you typically get the projects with user relations and do a foreach loop.

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Project extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class);
    }
}

$projects = Project::with('users')->get();
 
foreach ($projects as $project) {
    print $project->id . ': ' . $project->title . ' (';
 
    foreach ($project->users as $user) {
        print $user->email .'; ';
    }
 
    print ')<hr />';
}

This is a typical default simple belongs-to-many relation.

Adding Timestamps to Pivot

What can we add to the pivot? For example, you want to know when that record was added. By default, pivot tables are without timestamps. To enable timestamps, we must first add columns to the migration.

Schema::create('project_user', function (Blueprint $table) {
    $table->foreignId('project_id')->constrained();
    $table->foreignId('user_id')->constrained();
    $table->timestamps(); 
});

But that wouldn't be enough. Timestamps, by default, wouldn't be set in a pivot table. To enable setting timestamps in the relationship belongs-to-many, you need to define withTimestamps().

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Project extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class)->withTimestamps(); 
    }
}

I have re-seeded the database, and now the timestamps are filled.

How do we show the created_at from the pivot table? On the object, first, you must use the pivot, which is the intermediate table, and then call the column.

$projects = Project::with('users')->get();
 
foreach ($projects as $project) {
    print $project->id . ': ' . $project->title . ' (';
 
    foreach ($project->users as $user) {
        print $user->email .' <i>' . $user->pivot->created_at . '</i>; '; 
    }
 
    print ')<hr />';
}

Now, as a result, we can see email and timestamp.

Adding Custom Fields to Pivot

Similarly, you can add any field, not just the timestamps. Again, it was a similar scenario—migrations, then model, and then where do you use that? For example, we can add if it is active.

Schema::create('project_user', function (Blueprint $table) {
    $table->foreignId('project_id')->constrained();
    $table->foreignId('user_id')->constrained();
    $table->boolean('is_active')->default(false); 
    $table->timestamps();
});

Next, you define withPivot() in the belongs to many relationships and provide all the columns.

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Project extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class)->withPivot('is_active')->withTimestamps(); 
    }
}

Then, where you want to show the value, you again use pivot on the object and then call the column.

$projects = Project::with('users')->get();
 
foreach ($projects as $project) {
    print $project->id . ': ' . $project->title . ' (';
 
    foreach ($project->users as $user) {
        print $user->email .' <u>' . $user->pivot->is_active . '</u> <i>' . $user->pivot->created_at . '</i>; '; 
    }
 
    print ')<hr />';
}

As a result, some records are active, and some aren't.

Query By Pivot Fields

What if you want to query by the pivot field is_active? You can do this on the Eloquent query whereHas(), but also you can define a specific relationship.

For example, we can define a relation only for is_active true. When describing the relation, instead of withPivot(), we must use the wherePivot() method.

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Project extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class)->withPivot('is_active')->withTimestamps();
    }
 
    public function activeUsers(): BelongsToMany 
    {
        return $this->belongsToMany(User::class)->wherePivot('is_active', true)->withTimestamps();
    }
}

Then, instead of eagerly loading the users relation, we must use activeUsers.

$projects = Project::with('activeUsers')->get(); 
 
foreach ($projects as $project) {
    print $project->id . ': ' . $project->title . ' (';
 
    foreach ($project->activeUsers as $user) {
        print $user->email .' <u>' . $user->pivot->is_active . '</u> <i>' . $user->pivot->created_at . '</i>; ';
    }
 
    print ')<hr />';
}

As a result, only users with is_active true on a pivot table are shown.

Rename "pivot"?

Another possibility is to rename the word pivot. What if you don't like the phrase pivot, or it's not 100% clear for you and your team, and you want to rename it for clarity? In the relationship, you may define as.

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Project extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class)->withPivot('is_active')->withTimestamps();
    }
 
    public function activeUsers(): BelongsToMany
    {
        return $this->belongsToMany(User::class)
            ->as('active_user') 
            ->wherePivot('is_active', true)
            ->withPivot('is_active')->withTimestamps();
    }
}

Then, when showing data, instead of pivot for the intermediate table, you use active_user.

$projects = Project::with('activeUsers')->get();
 
foreach ($projects as $project) {
    print $project->id . ': ' . $project->title . ' (';
 
    foreach ($project->activeUsers as $user) {
        print $user->email .' <u>' . $user->active_user->is_active . '</u> <i>' . $user->active_user->created_at . '</i>; '; 
    }
 
    print ')<hr />';
}

The result stays the same. It still works. To be honest, I've never used this feature. I like the word pivot, and it describes the table best, but it is possible to rename it if you want.

Code for this lesson can be found on GitHub.