Laravel find in set排序实例
做项目遇到个需求，需要对结果集中的数据进行指定规则的顺序排列。
例如，用户状态有四种：
=>未激活；1=>正常；2=>禁用；3=>软删除
现在的需求是，我要按照：正常->未激活->禁用->删除；这个顺序来进行排序，同时按照注册时间降序，网上查了很多资料，国内提到这个的很少，在stackOverFlow上找到了答案！
先上解决方案：
publicfunctionindex($customer_type=null){
$search=request('search');
$perPage=request('perPage')?request('perPage'):10;
$customer_type=$customer_type?$customer_type:request('customer_type');
//\DB::enableQueryLog();
$data=Customer::select(['id','email','user_name','nick_name','status','phone','create_time'])
->where('customer_type','=',$customer_type)
->where(function($query)use($search){
if($search){
$query->where('user_name','likebinary','%'.$search.'%')
->orWhere('nick_name','likebinary','%'.$search.'%')
->orWhere('phone','likebinary','%'.$search.'%')
->orWhere('email','likebinary','%'.$search.'%');
}
})
->orderByRaw("FIELD(status,".implode(",",[1,2,0,3,4]).")")
->orderBy('create_time','desc')
->paginate($perPage);
//$query=\DB::getQueryLog();
//dd($data);
//追加额外参数，例如搜索条件
$appendData=$data->appends(array(
'search'=>$search,
'perPage'=>$perPage,
));
returnview('admin/customer/customerList',compact('data'));
}
打印出来的sql语句如下：
array:2[▼ =>array:3[▼ “query”=>“selectcount(*)asaggregatefromcustomerwherecustomer_type=?” “bindings”=>array:1[▼ =>“1” ] “time”=>2.08 ] =>array:3[▼ “query”=>“selectid,email,user_name,nick_name,status,phone,create_timefromcustomerwherecustomer_type=?orderbyFIELD(status,1,2,0,3,4),create_timedesclimit10offset0” “bindings”=>array:1[▼ =>“1” ] “time”=>1.2 ] ]
参考了以下链接：
https://stackoverflow.com/questions/42068986/laravel-weird-behavior-orderbyrawfield
https://stackoverflow.com/questions/34244455/how-to-use-not-find-in-set-in-laravel-5-1
https://stackoverflow.com/questions/35594450/find-in-set-in-laravel-example/35594503
find_in_set复杂应用：
publicfunctionget_teacher_list($timeType,$name,$perPage=10,$personality=0,$teachingStyle=0,$ageType=0)
{
//\DB::enableQueryLog();
$result_data=DB::table('teacher_infoasti')
->select('ti.*')
->join('customer','customer.id','=','ti.customer_id')
->where(function($query)use($personality){
if(trim($personality)){
$query->whereRaw("find_in_set($personality,ti.label_ids)");
}
})
->where(function($query)use($teachingStyle){
if(trim($teachingStyle)){
$query->whereRaw("find_in_set($teachingStyle,ti.label_ids)");
}
})
->where(function($query)use($ageType){
if(trim($ageType)){
$ageType=explode('-',$ageType);
$query->whereRaw("DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0between$ageType[0]and$ageType[1]");
}
})
->where(function($query)use($timeType){
//1本周，2下周
if($timeType==1){
$query->where('ti.can_appointment_1',1);
}elseif($timeType==2){
$query->where('ti.can_appointment_2',1);
}else{
$query->where('ti.can_appointment_1','>',0)
->orWhere('ti.can_appointment_2','>',0);
}
})
->where(function($query)use($name){
if(trim($name)){
$query->where('ti.chinese_name','like','%'.$name.'%')
->orWhere('ti.english_name','like','%'.$name.'%');
}
})
->where('ti.status',1)
->orderBy('ti.total_teach_num','desc')
->orderBy('ti.total_star_num','desc')
->orderBy('ti.satisfaction','desc')
->orderBy('ti.comment_num','desc')
->orderBy('ti.english_name','asc')
->paginate($perPage);
//dd($result_data,\DB::getQueryLog());
return$result_data;
}
专门拿出来看一下：
$ids=array(1,17,2);
$ids_ordered=implode(',',$ids);
$items=User::whereIn('id',$ids)
->orderByRaw(DB::raw("FIELD(id,$ids_ordered)"))
->get();
以上这篇Laravelfindinset排序实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。