Many to Many relation and pivot

Hello everyone, hope you doing great.

I have users and roles table and role_user table for many to many relation. in role_user table i have a field called assigned_by which is the user id who attached the role to the user. i want to retrieve the assigned by user information using with(), eager loading.

any help?

tykus

1 year ago

Level 104

Using the withPivot method whenever defining the relation, you can get the ID only

// User
public function roles(): BelongsToMany
{
    return $this->belongsToMany(Role::class)->withPivot('assigned_by');
}

However, you probably want a User instance to represent the assignor? In that case, you will need an intermediate pivot model into which you can define an assignor belongs to relation:

// User
public function roles(): BelongsToMany
{
    return $this->belongsToMany(Role::class)
        ->using(RoleUser::class)
        ->withPivot('assigned_by');
}

// RoleUser
public function assignor(): BelongsTo
{
    return $this->belongsTo(User::class, 'assigned_by');
}

Then you can get the assignor using:

User::with('roles')->first() // a User instance with loaded `roles`
    ->pivot // the pivot model
    ->assignor; // the assigning User

1 like

karo0420

1 year ago

Level 1

@tykus Hello mate. it works perfectly, but the problem is, i want to use it with eager loading like:

Role::find(1)->users()->with('assignor')->get();

is it any way to use pivot property in with() like:

->with('pivot.assignor')

it doesn't work :(

muuucho

1 year ago

Level 11

@karo0420 You should try and write a function assignor() also in your User model.

karo0420

1 year ago

Level 1

@muuucho i did , but it doesnt work because pivot table , try to get pivot value in the function to inform relation use that key, but does't work too.

tykus

1 year ago

Best Answer

Level 104

@karo0420 this Eager-load Pivot Relations package still works even though there has been no updates for a while.

You would simply use the AjCastro\EagerLoadPivotRelations\EagerLoadPivotTrait in the models on either end of the BelongsToMany relation (User and Role), and then eagerload like this:

$role = Role::with('users.pivot.assignor')->find(1);

$role->users // Collection of users with that Role
// or
foreach($role->users as $user) {
    dump($user->pivot->assignor); // User model instance
}

1 like

Please or to participate in this conversation.