Laravel Many-to-many Pivot Table: Add Extra Column with Relation

Sometimes you need to add more fields to the many-to-many pivot table, and those fields may also have their own relationships.

Example: you have models User, Project, and Role.

Users may be assigned to different Projects, with a different Role in each project.

The pivot table structure would look like this:

project_user

• project_id (foreign key to projects)
• user_id (foreign key to users)
• role_id (foreign key to roles)

If that third field is a regular field, without a relationship, you define this in the Eloquent model:

app/Models/Project.php:

class Project extends Model {
 
    public function users() {
        return $this->belongsToMany(User::class)->withPivot('role_id');
    }
 
}

But since we have a relationship here, we can define a special Pivot model and add a relationship inside of it.

php artisan make:model ProjectUser --pivot

Then, in that Pivot model, you define the relationship to the Role.

app/Models/ProjectUser.php:

use Illuminate\Database\Eloquent\Relations\Pivot;
 
class ProjectUser extends Pivot
{
    public function role()
    {
        return $this->belongsTo(Role::class, 'role_id');
    }
}

Then, you provide that Pivot model as a part of your many-to-many relationship, with using():

app/Models/Project.php:

use App\Models\ProjectUser;
 
class Project extends Model {
 
    public function users() {
        return $this->belongsToMany(User::class)
            ->withPivot('role_id')
            ->using(ProjectUser::class);
    }
 
}

And then, finally, you can run this query:

$projects = App\Models\Project::with('users')->get();
foreach ($projects as $project) {
    echo $project->name. ': ';
    foreach ($project->users as $user) {
        echo $user->name . ' (' . $user->pivot->role->name . ') ';
    }
}

As you can see, we can access the role of each user by calling $user->pivot->role->name.

Notice: keep in mind that this code will unfortunately produce the N+1 query problem. During my testing, I haven't found a way to eager load the users.pivot.role with this or any other syntax. If you have achieved this, please write in the comment, and I will update the article with your solution.