这篇文章将为大家详细讲解有关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排序的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
