Laravel find in set排序实例

2020-09-10 07:18:36 来源:易采站长站 作者:

Laravel find in set排序实例,需求,我要,注册时间,未激活,拿出来

Laravel find in set排序实例

易采站长站,站长之家为您整理了Laravel find in set排序实例的相关内容。

做项目遇到个需求,需要对结果集中的数据进行指定规则的顺序排列。

例如,用户状态有四种:

=>未激活;1=>正常;2=>禁用;3=>软删除

现在的需求是,我要按照:正常->未激活->禁用->删除;这个顺序来进行排序,同时按照注册时间降序,网上查了很多资料,国内提到这个的很少,在stackOverFlow上找到了答案!

先上解决方案:

public function index($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', 'like binary', '%' . $search . '%')          ->orWhere('nick_name', 'like binary', '%' . $search . '%')          ->orWhere('phone', 'like binary', '%' . $search . '%')          ->orWhere('email', 'like binary', '%' . $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,  ));  return view('admin/customer/customerList', compact('data'));}

打印出来的sql语句如下:

array:2 [▼ => array:3 [▼“query” => “select count(*) as aggregate from customer where customer_type = ?”“bindings” => array:1 [▼ => “1”]“time” => 2.08] => array:3 [▼“query” => “select id, email, user_name, nick_name, status, phone, create_time from customer where customer_type = ? order by FIELD(status, 1, 2, 0, 3, 4), create_time desc limit 10 offset 0”“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 复杂应用:

public function get_teacher_list($timeType, $name, $perPage = 10, $personality = 0, $teachingStyle = 0, $ageType = 0){  // \DB::enableQueryLog();  $result_data = DB::table('teacher_info as ti')    ->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')+0 between $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();
以上就是关于对Laravel find in set排序实例的详细介绍。欢迎大家对Laravel find in set排序实例内容提出宝贵意见

微信扫一扫

易采站长站微信账号