Laravel 5.1雄辩的查询语法

Question

I have the following model relationships. If a user logs in as an employee, I want them to be able to get a list of employees for a their company and the roles they have been assigned:

class User {

   // A user can be of an employee user type
   public function employee()
   {
       return $this->hasOne('App\Employee');
   }

   // 
   public function roles()
   {
      return $this->belongsToMany('App\Role');
   }

 }

 class Employee {

    // employee profile belong to a user
    public function user()
    {
       return $this->belongsTo('App\User');
    }

    // employee belongs to a company
    public function company()
    {
       return $this->belongsTo('App\Company');
    }
 }


 class Company {

    public function employees()
    {
        return $this->hasMany('App\Employee'); 
    }
 }

But the following query doesnt work. I get error Column not found: 1054 Unknown column companies.id in WHERE clause:

    $employee = Auth::user()->employee;

    $companyEmployees = Company::with(['employees.user.roles' => function ($query) use ($employee) {
        $query->where('companies.id', '=', $employee->company_id)
              ->orderBy('users.created_at', 'desc');
     }])->get();     

The users and the employees table have a one to one relationship.
All employees have a base role type of employee in addition they may also have other roles such as manager, supervisor etc.

How do I write a query that gives me a company with all its employees and their roles?

I've tried to add a hasManyThrough relation to the Company model but that doesn't work either?

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

Answer 1

I think you're ring to get a list of coworkers for the current user and eager load the user and role?

$employee = Auth::user()->employee;
$companyEmployees = Company::with(['employees.user.roles')->find($employee->company_id); 

Or perhaps:

$companyEmployees = Company::find($employee->company_id)->employees()->with('user.roles')->get();
$sorted = $companyEmployees->sortBy(function($employee){ return $employee->user->created_at; }); 

That might be a more direct route. Is your employee id in the user table or vice versa? The eloquent relationships are easy to set backwards.