Get fields from table related to pivot table

At first glance I'd say the relationship has not been set up quite right. It looks like you're kind of using a pivot table (meant for a many-to-many relationship). Let me ask:

• How many Roles should a Staff member have?
• If only one, then why the pivot table? Just to track the extra information (like what User added the Role to the Staff member)?

Hey ya! Just wanted to point you towards this part of the documentation. I could be wrong, but I think that StaffRole has to extend Pivot and not Model... and you should probably also be leveraging withPivot().

Also worth looking at hasOneThrough() or hasManyThrough() as an alternative.

Hope this helps! Good luck!

@MATT.LIBERA - I have 7 roles I think in my table, and a Staff can have them all. They don't grant permissions, they're just informative.

@SQUIRE - Well I had a whole explanation written up and then accidentally clicked a link to leave the page... and so there goes that 15 minutes...

The short story is that @itsricky is pointing you in the right direction.

Take it a step at a time. A Staff member can have many Roles, a Role can belong to many Staff members. That's a classic many-to-many relationship.

In Laravel, you'd handle it this way:

class Staff extends Model
{
    public function roles()
    {
        return $this->belongsToMany('App\Role');
    }
}

class Role extends Model
{
    public function staff()
    {
        return $this->belongsToMany('App\Staff');
    }
}

And you'd create a pivot table that contains both of these model names, singular, and alphabetically (so role_staff):

Schema::create('role_staff', function(Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('role_id'); // or bigInteger or whatever you want to use
    $table->unsignedInteger('staff_id'); // same here
}

And that's it for the basic M2M relationship. We know from documentation that we can use $staff->roles()->attach($arrayOfRoleIdsHere) to attach Roles to a Staff member or detach() to remove, or my personal favorite sync() to pass in a complete list of what Roles the Staff member should have. That's all pretty basic stuff.

We also know that you can add custom fields to a pivot table:

Schema::create('role_staff', function(Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('role_id'); // or bigInteger or whatever you want to use
    $table->unsignedInteger('staff_id'); // same here
    $table->unsignedInteger('assigned_by');
}

When using attach() or sync() you would make sure to explicitly pass values for any extra columns:

$staff->roles()->attach([
    1 => ['assigned_by' => 12] // we are saying that User 12 attached this Role to this Staff member
]);

Then, on your relationships you'd need to make sure you pull the extra columns from pivot:

class Staff extends Model
{
    public function roles()
    {
        return $this->belongsToMany('App\Role')->withPivot('assigned_by');
    }
}

class Role extends Model
{
    public function staff()
    {
        return $this->belongsToMany('App\Staff')->withPivot('assigned_by');
    }
}

When querying you can access any pivot columns with ->pivot:

$staff = Staff::with('roles')->first();
foreach ($staff->roles as $role) {
    echo $role->pivot->assigned_by; // outputs 12 in our example
}

That's great, but the problem comes in that Laravel doesn't know that 12 is actually a User ID. It's just a number. So we need to go a step farther, and explicitly define this relationship. But what IS this relationship? Well, this transaction must belong to a User, and a User may have many of these transactions. To do this, we do what @itsricky pointed you toward and create a model for your pivot table:

use Illuminate\Database\Eloquent\Relations\Pivot;

class RoleStaff extends Pivot
{
    public $incrementing = true; // need this for the `id` column on `role_staff` table

    public function user()
    {
        return $this->belongsTo('App\User', 'assigned_by'); // use 'assigned_by' here to tell the relationship what column to match off of.
    }
}

class User extends Model
{
    public function role_staff()
    {
        return $this->hasMany('App\RoleStaff', 'assigned_by') // same logic here.
    }
}

Then, finally, you can wrap this up by altering your Role and Staff models once more, so that they know to use this intermediate pivot model:

class Staff extends Model
{
    public function roles()
    {
        return $this->belongsToMany('App\Role')
                    ->using('App\RoleStaff')
                    ->withPivot('assigned_by');
    }
}

class Role extends Model
{
    public function staff()
    {
        return $this->belongsToMany('App\Staff')
                    ->using('App\RoleStaff')
                    ->withPivot('assigned_by');

    }
}

Now you can:

$staff = Staff::first();
foreach ($staff->roles as $role) {
    echo $role->pivot->assigned_by->username; // or whatever, off of your App\User class
    echo $role->name; // still works to get the name of the role
}

I think that should do it for you. Hopefully this accurately reflects your use case and I haven't missed anything. Have a read through the doc @itsricky linked to for more info on the intermediary table - but I would definitely recommend at least refactoring to follow the M2M conventions that Laravel has established.

Good luck!!

@matt.libera

Hey man! Thanks for your time, your reply was amazing. It helped me understand it better and I read the documentation again with other mind and I could achieve what I was trying.

On my staff model:

public function roles() {
        return $this->belongsToMany('App\Roles','role_staff','staff_id','role_id');
    }

On my roles model:

public function roleStaff() {
        return $this->belongsToMany('App\Staff','role_staff','role_id','staff_id');
    }

My pivot table (I changed the name as you suggested), extending the pivot class:

public function staff() {
        return $this->belongsToMany('App\Staff')->using('App\RoleStaff');
    }

And in my controller:

public function getStaffMembers() {
        $staff = new Staff;
        return $staff::where('active',1)
                                ->orderBy('nickname','asc')
                                ->with(['addedByUser','roles'])
                                ->get();
    }

I really appreciate your answer, I'm starting to understand better Laravel and I'm beggining to love it!

@SQUIRE - Happy to help! I'm glad you're enjoying Laravel. I started a couple years ago and haven't looked back! Such a great development experience.