Managing Pivot Data with Laravel Eloquent

Laravel provides the most beautiful Object Relational Mapping systems I’ve ever used with Eloquent. Eloquent has so many powerful features making working with a database so enjoyable! One of my favorite features of Laravel Eloquent is the many-to-many relationship structure. When I had to write raw SQL, I always hated many-to-many relationships. With Eloquent, they are a breeze!

In this tutorial we’ll go through a few of my favorite methods when updating and creating many-to-many relationships with Laravel Eloquent. There are a lot of other basic and useful methods available for defining and querying these relationships.

However, I was always slightly confused when creating and updating related data so I wrote this tutorial as a guide for myself and for you. I’ve tended to use a lot of these to set up Gates & Policies and other permission structures in Laravel.

Set up a Many-To-Many Relationship with Laravel Eloquent

For this example, we are going to use a real life situation we came across when building Bugflow. That’s managing user permissions on projects and organizations. For this example, every user can belong to multiple projects and multiple organizations. On each project and organization, the user has permissions. These will be stored in the pivot table along with the relationship with the project. Let’s set this up.

First we will create 3 tables within our Laravel Install. The tables will be users , projects, and users_projects. For simplicity sake, I won’t be adding EVERY field, just enough to make sure this tutorial makes sense.

At the most basic level, our users table should look like:

users
	id
	name

Our projects table should look like:

projects
	id
	name

And our users_projects table should look like:

users_projects
	user_id
	project_id
	permission

Notice the permission column on the users_projects table? That column will determine the level of access the user has on a project. In future references, this table is also referred to as the pivot table.

For now, the values of the permission column could either be admin or member. I’ll document how to update this value efficiently. When creating the table, I always set the default permission column to be member. This way we can only directly set this value if needed and don’t accidentally create users with admin permissions.

Now that we have our tables constructed, let’s define our relationships in the App\Models\User.php and App\Models\Project.php Eloquent models respectively.

Defining our Relationships

Before we move into all of the methods regarding many-to-many relationships, we have to define the relationship on each of our models. With Eloquent you have to use the belongsToMany() relationship on both the User and the Project. First, let’s open our User model and add the projects relationship:

<?php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable;

    //... Other relationships, etc.

    public function projects()
    {
        return $this->belongsToMany('App\Models\Project', 'users_projects', 'user_id', 'project_id');
    }
}

When setting up many-to-many relationships with Eloquent, there are 4 parameters you need to set. The first parameter is the model you are relating to. In this case, it’s App\Models\Project. The second parameter is the table the relationship takes place on. So, we use our pivot table which is users_projects. The third parameter is the key on the pivot table that holds the key of the model we are relating. Our users all have an id field. On the pivot table, this is user_id. We use the user_id value. Finally, we select the related foreign key. This is the key on the pivot table which identifies the relationship to the project. In this case it’s project_id which will reference the id on the projects table.

Now when we look at our Project model, it should look very similar, just from the opposite perspective:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Project extends Model
{
    public function users()
    {
        return $this->belongsToMany('App\Models\User', 'users_projects', 'project_id', 'user_id');
    }
}

So now we can call $project->users() and $users->projects() to get all of the users on a project and all of the projects that belong to a user.

Since we now have our relationships setup, let’s take a look at all the different ways we can add and delete relationships.

Attaching with Pivot Data

One of the best features about Laravel is their docs provide so much helpful information. They cover a lot of the basics for many-to-many relationships, such as attach() and detach(). What I’m going to focus on are some of the more “edge cases” that I’ve run across.

If you were to bind a project to a user using attach(), the code would look like this:

$user->projects()->attach([1]);

or you could pass the project model itself:

$user->projects()->attach($project);

These are great, however they present a challenge if you want the permissions on the users_projects table to be anything other than member. Like if you were adding an admin to a project. To do that, you’d have to pass along the permission with the id of the project you are attaching:

$user->projects()->attach([1 => [
    'permission' => 'admin'
]]);

Now the attach method takes a multi-dimensional array with the key being the id of the project we are attaching and the sub array being an array mapped to the columns of the pivot table. If you wanted to attach multiple projects with different permissions, you could do:

$user->projects()->attach([
    1 => [
        'permission' => 'admin'
    ],
    2 => [
        'permission' => 'member'
    ]
]);

Or if you wanted to attach multiple projects with the same permission, you could simplify the above code to be:

$user->projects()->attach([1, 2], [
    'permission' => 'member'
]);

These little methods are super helpful! I tend to use multiple forms of the attach() method throughout the project in different scenarios. However, I only use a form of attach() if I know the relationship has no possibility of existing. If there’s a possibility the relationship exists (i.e. the user is already on the project), then it’s possible to bind them twice. That’s not convenient. For that I use a form of sync() which is coming up next!

Syncing Relationships

Any time I don’t know if a relationship already exists, I use a form of sync(). The sync() method is another way of updating relationships between two models. The difference is that the pivot table will always remain in-sync with what is passed.

What this means, is if I run the following code:

$user->projects()->sync([1]);

The user will only be bound to project ID 1. If I run that code thirty times, the relationship will only be bound once in the table and it will stay in sync (get it?). However, this becomes an issue if your user has twenty projects, you don’t know if they have project ID 2 and you run:

$user->projects()->sync([2]);

The other 19 projects that the user had have now been deleted! That’s where syncWithoutDetaching() comes in handy.

Sync Without Detaching

The syncWithoutDetaching() method is extremely useful. This way you can add a user to a project IF they have not already been added to the project! Solving a few of the caveats of attach() and sync().

To attach a project to a user, not knowing if they are already on the project, you’d run:

$user->projects()->syncWithoutDetaching(3);

Now if the user already has the project, nothing will happen. However, if the user has not been added to the project, this this method will add the user to the project.

You can also attach pivot data:

$user->projects()->syncWithoutDetaching([3 => [
    'permission' => 'admin'
]]);

Now you will make sure that if the user has the project, they are an admin on the project. Pretty slick!

Sync With Pivot Values

Similar to how you can pass an array that attaches a permission to every relationship, you can do that with sync as well. The method is syncWithPivotValues(). In Bugflow, we do this when we upgrade a user’s member ship on an organization. So if the organization has 15 projects and the user gets upgraded to an admin we want to pass an array of projects and have all of them be bound to the user with proper permission. To do that, you can run:

$user->projects()->syncWithPivotValues([1, 2, 3], [
    'permission' => 'admin'
]);

Now the user will have permissions for all projects with the level of admin. While on the subject of updating pivot table data, let’s check out one of my favorite methods, updateExistingPivot(). You will see why in a second.

Updating Existing Pivot Table

When working with a many-to-many relationship with Eloquent, you are mapping objects to a database table. Well, what if you have a pivot table and you want to update one of the pivot fields. You could either build a model like UserProject.php and map to that table. But that requires you to have an id on the users_projects table. The pivot table is pretty dynamic so the id would become kind of a pain and your auto increments could be large for no reason.

You could also use the DB facade and directly query the pivot table. That’s nice, but there’s a better option.

The updateExistingPivot() option solves all of these problems! What this method does is takes a relationship on the pivot table, in our case users_projects and updates a pivot value such as permission.

Let’s say we want to update the permission on project 2 to be admin:

$user->projects()->updateExistingPivot( 2, ['permission' => 'admin'] );

That’s all you have to do! Now you can update whatever pivot table data exists without building intermediate models or direct access to the database.

Conclusion

Hope these little snippets and explanations help you just as much as they help me! For all of the Eloquent documentation check out the official docs. Figured I’d share a few of my own experiences. Sometimes I get confused at what I should use, so it’s good to have something to reference.

By all means, if you have questions, feel free to reach out on Twitter (@danpastori). If you like these tutorials, sign up for our mailing list.