Laravel 5.1雄辩的查询语法

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');
}

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.

Users::select('table_users.id')->with('roles')->join('table_employes', function($join) use ($employee) {

    $join->on('table_employes.user_id','=','table_users.id')->where('table_employes.company_id', '=', $employee->company_id);

})->orderBy('tables_users.created_at')->get();

1. Create relationship for database table columns in migrtaion :

User Role

$table->foreign('user_id')->references('id')->on('users');

Users

$table->increments('id');

2. Create a model for each database table to define relationship

User.php (model)

    public function userRoles()
    {
        return $this->hasOne('App\UserRoles', 'user_id', 'id');

    }

Userroles.php (model)

public function user()
    {
        return $this->belongsTo('App\User', 'user_id', 'id');
    }

3. Let controller handle database calls recommended to use REST api

Controller

    use App\User;
    use App\UserRoles;

 class UserController extends Controller
{

    public function index()
        {
            return User::with('userRoles')->orderBy('users.created_at', 'desc')->paginate(50);
        }
}