CODEWITHSUNDEEP
laravelpivot-tablehas-and-belongs-to-many
Cat Named Dog
Cat Named Dog

Reputation: 1698

laravel: How to get column related to another column in pivot table (3 column pivot)

I'm not sure how to phrase this question, but I need to do the following:

I have a pivot table with three columns user_id, account_id, role_id

So in my database if I have these rows on account_role_user (pivot)

|---------------------|------------------|-------------
|     account_id      |     user_id      |   role_id  |
|---------------------|------------------|-------------
|          1          |         1        |     1      |
|---------------------|--------------------------------
|          2          |         2        |     1       |
------------------------------------------------------
|          1          |         2        |     3      |
------------------------------------------------------

Role - 1 = owner

Role - 3 = manager

What I'm trying to do is get users on a specific account with their specific role. But I'm only able to query things like App\User::with('accounts.roles')->whereId(2)->first(); which does show the users on an account, but it groups their roles that aren't specific to the account.

I only want the users and their roles that are relevant to the account.

If I'm querying account with id of 1 and I want to list users on that account with their role, I should have something like:

$account = App\Account::find(1)

User with ID of 1 has a role id of 1 on the account id of 1

User with ID of 2 has a role id of 3 on the account id of 1

User Model:

    public function accounts() {
        return $this->belongsToMany('App\Account', 'account_role_user', 'user_id', 'account_id');
    }

    public function roles() {
        return $this->belongsToMany('App\Role', 'account_role_user', 'user_id', 'role_id');
    }

Account model:

    public function users() {
        return $this->belongsToMany('App\User', 'account_role_user', 'account_id', 'user_id');
    }

    public function roles() {
        return $this->belongsToMany('App\Role', 'account_role_user', 'account_id', 'role_id');
    }

Role Model:

   public function users() {
    return $this->belongsToMany('App\User', 'account_role_user', 'role_id', 'user_id');
   }

   public function accounts() {
      return $this->belongsToMany('App\Account', 'account_role_user', 'role_id', 'account_id');
     }

Upvotes: 2

Views: 863

Answers (1)

Cat Named Dog
Cat Named Dog

Reputation: 1698

Thanks to someone's help on Laravel's Discord channel, I figured this out.

On the Account Model, I changed the users() relation to:

    public function users() {
        return $this->belongsToMany('App\User', 'account_role_user')
        ->using('App\AccountRoleUser')
        ->withPivot('role_id');
    }

I created a pivot model called AccountRoleUser

namespace App;


use Illuminate\Database\Eloquent\Relations\Pivot;

class AccountRoleUser extends Pivot
{
    public function role() {
        return $this->belongsTo('App\Role');
    }
}

And on the User model, I changed the accounts() relation to:

    public function accounts() {
        return $this->belongsToMany(Account::class, 'account_role_user')->withPivot('role_id');
    }

Now my query is really succinct and logical:

$account = auth()->user()->accounts->where('foo', 'bar')->first();
$users = $account->users;

Each User inside the collection has a pivot object now coming from the pivot model.

Which means I can now do something like:

@foreach ($users as $user)
  <p>This is user {{ $user->first_name }} {{ $user->last_name }} with a role of 
   {{$user->pivot->role->description}} 
</p>
@endforeach

Upvotes: 3

Related Questions