laravel时间日期字段比较
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排序实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持开心学习网。
原文链接：https://blog.csdn.net/zhezhebie/article/details/78357354