Laravel 8.x - Diving into Pivot Tables

pivot-tables.md

Laravel 6 - Diving Into Pivot Tables

Pivot tables can be confusing and a little hard to wrap your head around at first. In this quick article we are going to dive into what a pivot table is, how to create one and finally how to use the pivot table. Let's dive in!

What is a pivot table?

A pivot table is used to connect relationships between two tables. Laravel provides a Many To Many relationship where you can use a pivot table.

The use of a pivot table

In the example next, we will review how a users table and a roles table are joined together with a user_roles pivot table. Pivot tables allow you to (in this case) add multiple roles to a user.

Example of a Pivot Table

Let's create two tables which we need to use the Many To Many relationship for.

• Table: users
• Table: roles
• (Pivot) Table: user_roles

The users table will look something like this:

• id (INT)
• name (VARCHAR)

The roles table will look like this:

• id (INT)
• name (VARCHAR)

Now our pivot table user_roles will look like this:

• user_id (INT)
• role_id (INT)

Using Pivot Tables

So now that we've gone over the structure of the two tables and our pivot table, let's dig into how to use them!

Creating The Pivot Table's Migration

The migration should look like this, it establishes the proper columns and foreign key relations to both the users and roles table. The migration will also set both user_id and role_id as the primary keys so there cannot be duplicates with both the same user_id and role_id.

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUserRolesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('user_roles', function (Blueprint $table) {
            $table->integer('user_id')->unsigned()->index();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->integer('role_id')->unsigned()->index();
            $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
            $table->primary(['user_id', 'role_id']);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('user_roles');
    }
}

Setting Up The Model Relations

We will open our User.php and Role.php models and define the relations needed in both models.

<?php

namespace App\Models;

use App\Models\Role;
use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    use Notifiable;
    
	/*
     * The roles that belong to this user
     */
    public function roles()
    {
        return $this->belongsToMany(Role::class);
    }
}

<?php

namespace App\Models;

use App\Models\User;
use Illuminate\Database\Eloquent\Model;

class Role extends Model
{    
	/*
     * The users that belong to this role
     */
    public function users()
    {
        return $this->belongsToMany(User::class);
    }
}

Defining Your Custom Intermediate Table

If you want to define the custom model that represents the pivot or intermediate table of your relation, you can call the using method when defining the relationship. Note that custom Many To Many pivot models should extend the Illuminate\Database\Eloquent\Relations\Pivot class. For example, we want to define a Role which uses a custom UserRole pivot model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Relations\Pivot;

class UserRole extends Pivot
{    
    //
}

Retrieving a User's Role

Let's assume our User object has many Role objects that it is related to. After accessing this relationship, we may access the intermediate table using the pivot attribute on the models.

$user = App\Models\User::first();

foreach ($user->roles as $role) {
    echo $role->name; // This will echo out the role name
}

Notice that each Role model we retrieve is automatically assigned a pivot attribute. This attribute contains a model representing the intermediate table, and may be used like any other Eloquent model.

Customizing The pivot Attribute Name

As mentioned above, attributes from the intermediate table may be accessed on models using the pivot attribute. However, you can customize the name of this attribute to better reflect its purpose within your application.

For example, if your application contains users that may subscribe to multiple podcasts, you probably have a many-to-many relationship between users and podcasts. If this is the case, you may wish to rename your intermediate table accessor to subscription instead of pivot. This can be done using the as method when defining the relationship:

public function podcasts()
{
    return $this->belongsToMany(Podcast::class)
                ->as('subscriptions');
}

Once this is done, you can access the intermediate table data using the customized name from above:

$users = App\Models\User::with('podcasts')->get();

foreach ($users->flatMap->podcasts as $podcast) {
    echo $podcast->name; // This will echo out the podcast name
}

You may be asking what flatMap does in the above code example. It's a Collection helper. Eloquent returns a Collection on a relation by default. We use flatMap in the example to iterate through the collection and pass each value to the given callback. The callback is free to modify the item and return it, thus forming a new collection of modified items. Then, the array is flattened by a level (which is what we want in this case).

Adding A Role To A User

$user = App\Models\User::first(); // John Doe
$role = App\Models\Role::first(); // Admin

$user->roles()->attach($role->id);

Adding Multiple Roles To a User

You can pass an array of IDs to the attach method to add multiple roles to a user.

$user = App\Models\User::first(); // John Doe
$roles = App\Models\Role::all()->pluck('id'); // Admin, User

// Adds MULTIPLE roles to the user
$user->roles()->attach($roles);

Removing A Role From A User

$user = App\Models\User::first(); // John Doe
$role = App\Models\Role::first(); // Admin

$user->roles()->detach($role->id);

Removing Multiple Roles From a User

This works the same as above when adding a role but you can pass an array of IDs to the detach method to remove multiple roles from a user.

$user = App\Models\User::first(); // John Doe
$roles = App\Models\Role::all()->pluck('id'); // Admin, User

// Removes MULTIPLE roles to the user
$user->roles()->detach($roles);

Syncing Roles

There's a method called sync that accepts new vales as an array of IDs and will take care of the syncing of roles. The result is that no matter what values were in the table prior to running sync, after the code is run there will only be these role IDs attached to the user. It will remove any not in this list that are in the database and add any that aren't in the database but that are in this list.

$user  = App\Models\User::first();
$roles = App\Models\Role::all()->pluck('id');

// $roles will provide an array of role IDs that will be synced to the user 
$user->roles()->sync($roles);

Attaching a Role with Additional Parameters

Sometimes you may want to add additional parameters when attaching a role to a user. Let's take a look at an example on how to do this. In this example, assume the Administrator is assigning a user a new role but they want to specify who it was added by (just a string for simplicity sake, assume added_by is a column on the user_roles table alongside user_id and role_id).

$user = App\Models\User::first(); // John Doe
$role = App\Models\Role::find(2); // User

$user->roles()->attach($role->id, ['added_by' => 'Braunson']);

Wrapping up

There's a bunch more things you can do with relations and they are documented in the Laravel documentation. The goal of this write-up was to establish a basic understanding and usage with pivot tables in Laravel.

@Farouk615
I was wondering the same.
I am stuck on a project that requires 3 tables all linked in a ManyToMany relation which I think would require a pivot table with 3 foreign keys.

I hope someone can help out ASAP

Checkout its by someone on the laravel core team
https://github.com/staudenmeir/eloquent-has-many-deep

is it possible to use sync even it is not an id???