您的位置:首页 > 编程学习 > > 正文

laravel时间日期字段比较(Laravel find in set排序实例)

更多 时间:2021-10-10 00:58:12 类别:编程学习 浏览量:1450

laravel时间日期字段比较

Laravel find in set排序实例

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

例如,用户状态有四种:

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

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

先上解决方案:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 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语句如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 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 复杂应用:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 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;
  • }
  • 专门拿出来看一下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • $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

    您可能感兴趣