Laravel 5.1 hasManyThrough relationships and pivot table

Question

I have the following models / tables setup in my application;

timesheet

• user_id

user

• id

supervisor_user

• user_id
• supervisor_id

Users get "assigned" to supervisors via the supervisor_user pivot table.

I have the following relationship setup on the User model, which gets supervisor users.

/**
 * The supervisors that are assigned to the user.
 *
 * @return Object
 */
public function supervisors()
{
    return $this->belongsToMany('App\Models\User\User', 'supervisor_user', 'user_id', 'supervisor_id')->withTimestamps();
}

I want to setup another relationship which gets a list of timesheets "assigned" to a supervisor. I'm guessing with hasManyThrough relationship...but not exactly how to write the code for it.

How can I achieve what I need?

Answer 1

In your Role model make a method:

public function users()
{
    return $this->belongsToMany('App\Models\User\User', 
           'supervisor_user', 'supervisor_id', 'user_id')->withTimestamps();
}

In your User model make a method:

public function timesheets()
    {
        return $this->hasMany('App\Timesheet', 'user_id');
    }

And then make a call:

$supervisorRole = Role::find($id);

foreach($supervisorRole->users as $user)
    $timesheets = $user->timesheets;
endforeach

Answer 2

Yes, you are right you can achieve timesheets for supervisor using two methods. Either use join query, that is joining multiple tables and finding the timesheets or using hasManyThrough.

The Easy way: because you have user_id in supervisor table and I assume that is the user that belongs to the given supervisor. We can simply compare supervisor_users.user_id with timesheets.user_id.

$timesheets = db:table('timesheets')
->join('supervisor_user','timesheets.user_id','=','supervisor_users.user_id')->get();

This should do the trick. If you need to add where or select clause then add before get() thing.

The faster way : If you check the laravel documentation, for hasmanythrough, you would probably have a case where its one to many and again one to many. So like: country has many users and users have many posts. In this if we do hasmanythrough we can pull posts that belongs to country. And for you what I see is, A single supervisor has many users and a user has many timesheet. Please correct me here if I am wrong my solution is based on that, so I will help u get timesheets for a given supervisor. Inshort all the timesheets that belong to a user which is again belonged to a given supervisor. You will have all the timesheets for all the users for a given supervisor.

public function timesheets()
    {
        return $this->hasManyThrough('App\SupervisorUser', 'App\User');
    }

Here argument one is the final table from which you want data to be. And argument two is your intermediate table we passing through. You can add third and fourth argument if you like for Id's. Now simply try : $supervisor_timesheet = $Supervisor_user->timesheets