Laravel: Pivot Table With An Example

In this sourcebook, we will see what is Laravel pivot table and its example. Tighten your seat belts and take a deep breath. Let’s go.

Table of Contents

1. What Is Pivot Table In Laravel

2. Example Of Pivot Table In Laravel

3. Retrieving Pivot Table Columns

4. Rename Pivot Attribute Name In Laravel

01 What Is Pivot Table In Laravel

While working with Many to Many relationships in Laravel we need to introduce an intermediate table which is called the Pivot table in Laravel terms.

Using the pivot attribute we can easily interact with this intermediate table in the models or in controllers.

02 Example Of Pivot Table In Laravel

Let’s say in our project, we need to define the roles of every user. So users can have many roles and inverse roles can have many users. Thus, it will be a Many To Many relationships.

To accomplish this we need to create 3 tables users, roles, and intermediate table role_user. The role_user table will have the user_id and role_id column which connects both users and roles table and this intermediate table called the pivot table in Laravel.

Here are the table structures:

users
    id - integer
    name - string

roles
    id - integer
    name - string

role_user
    user_id - integer
    role_id - integer

We have the following data in our database

users

id  name        email            
--  -------     -------
1   John Doe 1  johndoe+1@example.com
2   John Doe 2  johndoe+2@example.com
3   John Doe 3  johndoe+3@example.com
4   John Doe 4  johndoe+4@example.com

role

id  name        created_at            updated_at
--  ----------  -------------------   ------------------
1   Admin       2021-05-27 13:00:32   2021-05-27 13:00:32
2   Subscriber  2021-05-27 13:00:32   2021-05-27 13:00:32
3   Editor      2021-05-27 13:00:32   2021-05-27 13:00:32
4   Viewer      2021-05-27 13:00:32   2021-05-27 13:00:32

role_user

id  user_id   role_id  active  created_by  created_at            updated_at   
--  -------   -------  ------  ----------  -------------------   ------------------
1      1         1       1          1      2021-05-27 13:00:32   2021-05-27 13:00:32
2      2         2       0          1      2021-05-27 13:00:32   2021-05-27 13:00:32
3      3         2       1          1      2021-05-27 13:00:32   2021-05-27 13:00:32
4      4         3       1          1      2021-05-27 13:00:32   2021-05-27 13:00:32

03 Retrieving Pivot Table Columns

As you can see in the above role_user table we have many extra columns (active, created_at, updated_at) than the model keys. What if I would like to retrieve the active and created_by column of an intermediate table?

To do that first we need to tell the model to fetch those column while defining the relationship:

return $this->belongsToMany(Role::class)->withPivot('active', 'created_by');

After that, we can access those using the pivot attribute in our Controller like below:

use App\Models\User;

$user = User::find(1);

foreach ($user->roles as $role) {
    echo $role->pivot->created_by;
    echo $role->pivot->active;
}

If you would like to retrieve the default timestamps (created_at, updated_at) using the pivot attribute then you can use the withTimestamps()

return $this->belongsToMany(Role::class)
            ->withTimestamps()
            ->withPivot('active', 'created_by');

04 Rename Pivot Attribute Name In Laravel

Isn’t it good to have a better relative name of pivot attribute like subscriptions, comments, order_items, etc. instead of just pivot name.

If you want to rename "pivot" word to something else then you just use as('name') instead of as('pivot') in your relationship like below:

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

Then you can use it in your in Controllers like:

$podcasts = $user->podcasts();
foreach ($podcasts as $podcast) {
       // instead of $podcast->pivot->created_at;
       echo $podcast->subscription->created_at;
}

Additionally, read our guide:

That’s it from our end. We hope this article helped you to learn the pivot table in Laravel with an example.

Please let us know in the comments if everything worked as expected, your issues, or any questions. If you think this article saved your time & money, please do comment, share, like & subscribe. Thank you for reading this post 🙂 Keep Smiling! Happy Coding!

Laravel: Pivot Table With An Example

01 What Is Pivot Table In Laravel

02 Example Of Pivot Table In Laravel

03 Retrieving Pivot Table Columns

04 Rename Pivot Attribute Name In Laravel

Leave a Comment Cancel reply