Laravel find in set排序实例

砸漏

发布于 2020-10-20 14:18:12

8950

发布于 2020-10-20 14:18:12

文章被收录于专栏：恩蓝脚本

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

例如，用户状态有四种：

= 未激活；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排序实例就是小编分享给大家的全部内容了，希望能给大家一个参考。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020-09-11 ，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法