在 Laravel 中按 inside with() 进行排序

Question

我有一个控制器，它根据get查询中收到的变量过滤表中的列，然后它根据变量的id返回另一个表的值得到，但是现在对于$filter_by_name条件，我想按用户表中的first_name列进行过滤， 请问我该怎么做，即我想返回按其first_name列排序的用户表

数据库结构

公司-用户表

id  company_id  user_id role created modified active department_level

用户表

id 
first_name 
last_name 
email 
password 
active_company 
profile_photo_id 
verified 
active 
remember_token 
created 
modified

公司用户控制器

public function getCompanyUsers($companyId)
{
$filter = strtolower(Input::get('filter'));
if($filter && $filter === 'on' ){
$filter_by_date = strtolower(Input::get('filter_by_date'));
$filter_by_name = strtolower(Input::get('filter_by_name'));
$filter_by_role = strtolower(Input::get('filter_by_role'));
if($filter_by_date){
if($filter_by_date == 'oldest'){
$users = CompanyUser::where('company_id', $companyId)->orderBy('created', 'DESC')
->with(['user','user.userDepartments','user.userDepartments.department'])->get();
return $users;
}else{
$users = CompanyUser::where('company_id', $companyId)->orderBy('created', 'ASC')
->with(['user','user.userDepartments','user.userDepartments.department'])->get();
return $users;
}
}elseif ($filter_by_name){
if($filter_by_name == 'ascending'){
$users = CompanyUser::where('company_id', $companyId)->orderBy('first_name', 'ASC')
->with(['user','user.userDepartments','user.userDepartments.department'])->get();
return $users;
}else{
$users = CompanyUser::where('company_id', $companyId)->orderBy('first_name', 'DESC')
->with(['user','user.userDepartments','user.userDepartments.department'])->get();
return $users;
}
}elseif($filter_by_role){
if($filter_by_role == 'member'){
$users = CompanyUser::where(['company_id' => $companyId,'role'=>'Member'])->with(['user','user.userDepartments','user.userDepartments.department'])->get();
//  dd($users);
return $users;
}elseif($filter_by_role == 'manager'){
$users = CompanyUser::where(['company_id' => $companyId,'role'=>'Manager'])->with(['user','user.userDepartments','user.userDepartments.department'])->get();
return $users;
}else
$users = CompanyUser::where(['company_id' => $companyId,'role'=>'Admin'])->with(['user','user.userDepartments','user.userDepartments.department'])->get();
return $users;
}
}
$users = CompanyUser::where('company_id', $companyId)->
with(['user','user.userDepartments','user.userDepartments.department'])->get();
//dd($users);
return $users;
}

Answer 1

您可以在急切加载时传递更紧密的函数以添加这样的约束，请参阅Laravel文档的更多信息：

$users = CompanyUser::with(['user'=> function ($query) {
$query->orderBy('first_name', 'desc');
}
])
->where('company_id', $companyId)
->get();

Answer 2

您必须在如下所示的关系模型上执行 orderBy：

$users = CompanyUser::where('company_id', $companyId)
->with(['user' => function($subQuery){
$subQuery->orderBy('first_name', 'ASC');
}])
->with(['user.userDepartments','user.userDepartments.department'])
->get();
return $users;

Answer 3

一种方法是在"公司用户"表中添加"first_name"列

现在您可以按('first_name'，'ASC'(进行排序。 我认为这不是最好的方法，但我脑海中没有其他想法。