Laravel Relationship - Additional fields in pivot table linking to additional models

Question

I have simplified the problem to get to the point. I have three tables, users, roles, account.

Normally I would set up the User model to have a many to many relationships with roles but I want those roles to be specific to each account. So I have added an additional field to the pivot table. Here are the tables and fields that I have;

‘users’ table

|—————————

| id | name |

|—————————

| 1 | Bob    |

| 2 | Jim     |

| 3 | Fred   |

|—————————

‘roles’ table

|—————————

| id | title |

|—————————

| 1 | Administrator    |

| 2 | Manager           |

| 3 | Approver           |

|—————————

‘accounts’ table

|—————————

| id | name |

|—————————

| 1 | ABC Company    |

| 2 | XYZ Shipping     |

| 3 | KLM Transport   |

|—————————

I then have the pivot table role_user with an additional pivot field for the account;

|—————————

| role_id | user_id | account_id

|—————————

| 1         | 3           |  1

| 2         | 2           |  1

| 3         | 2           |  3

| 3         | 1           |  2

|—————————

I have used the withPivot function on the belongsToMany function when setting up the many to many relationships. This allows me to get the information using $user->roles->pivot->account_id but what I need is to be able to get the name of that company. All it’s passing to the blade template is the id from the pivot table and not linking that to an actual Account model.

Is there a way with Eloquent to get this entire model in the same way as the original relationship?

What is the Laravel version you are using? Also, What is the relationship between the User/Role and the Account models? — Ijas Ameenudeen
Laravel 5.8... the User can have many roles and each one will be associated with a specific account. So User/Role is ManyToMany but I want to specify which Account for each User/Role pair. — B Gregory
So Role belongTo an Account & an Account can have many Roles, right? — Ijas Ameenudeen
Not quite. Users can have a Role in each Account. For example, User1 may be an Administration of AccountA but may only be an Author in AccountB so that’s why I need to link the Account ID in the pivot table. That’s fine but all I can then receive is the Account ID and not anything else about the Account Model. — B Gregory

Welder Lourenço Welder Lourenço · Accepted Answer · 2019-08-01T17:42:00

Create a Custom Pivot Model

use Illuminate\Database\Eloquent\Relations\Pivot;

class RoleUserAccountPivot extends Pivot
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function role()
    {
        return $this->belongsTo(Role::class);
    }

    public function account()
    {
        return $this->belongsTo(Account::class);
    }
}

Update your belongsToMany relationships

Bellow is an example with the User::roles relationship

class User //extends...
{
    public function roles()
    {
        return $this->belongsToMany(Role::class, /*other parameters*/)->using(RoleUserAccountPivot::class)->withPivot('account_id');
    }
}

Usage

$user->roles->first()->pivot->account // returns Account model

Hope it helps.

Reference links:

Laravel doc on custom pivots

Laravel Relationship - Additional fields in pivot table linking to additional models

1 Answers

Create a Custom Pivot Model

Update your belongsToMany relationships

Usage