Laravel find in set排序?qū)嵗?/h1>
更新時(shí)間:2019年10月09日 11:09:28 作者:SHUIPING_YANG
今天小編就為大家分享一篇Laravel find in set排序?qū)嵗?,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧
做項(xiàng)目遇到個(gè)需求,需要對(duì)結(jié)果集中的數(shù)據(jù)進(jìn)行指定規(guī)則的順序排列。
例如,用戶狀態(tài)有四種:
=>未激活;1=>正常;2=>禁用;3=>軟刪除
現(xiàn)在的需求是,我要按照:正常->未激活->禁用->刪除;這個(gè)順序來進(jìn)行排序,同時(shí)按照注冊(cè)時(shí)間降序,網(wǎng)上查了很多資料,國(guó)內(nèi)提到這個(gè)的很少,在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);
//追加額外參數(shù),例如搜索條件
$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 復(fù)雜應(yīng)用:
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排序?qū)嵗褪切【幏窒斫o大家的全部?jī)?nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
您可能感興趣的文章:
相關(guān)文章
-
教你如何用php實(shí)現(xiàn)LOL數(shù)據(jù)遠(yuǎn)程獲取
LOL(英雄聯(lián)盟)最近非常的火爆,哥自然也在玩了,最近遇到個(gè)問題,就是每次想看看自己的戰(zhàn)斗力啥的,還得先開盒子等等,麻煩,最近有一個(gè)想法,打算把它實(shí)現(xiàn)出來。 2014-06-06
-
Laravel框架學(xué)習(xí)筆記(二)項(xiàng)目實(shí)戰(zhàn)之模型(Models)
上一篇已經(jīng)介紹開發(fā)環(huán)境的搭建,這篇將從項(xiàng)目實(shí)戰(zhàn)開發(fā),一步一步了解laravel框架。首先我們來了解下laravel框架的模型
(Models) 2014-10-10
-
PHP網(wǎng)頁游戲?qū)W習(xí)之Xnova(ogame)源碼解讀(一)
這篇文章主要介紹了PHP網(wǎng)頁游戲Xnova(ogame)源碼解讀,需要的朋友可以參考下 2014-06-06
-
php array_walk_recursive 使用自定的函數(shù)處理數(shù)組中的每一個(gè)元素
php中,如果需要對(duì)數(shù)組中的每個(gè)元素進(jìn)行一定規(guī)則的處理,我們可以使用array_walk_recursive,array_walk_recursive函數(shù)用于對(duì)數(shù)組中的每個(gè)成員遞歸地應(yīng)用用戶函數(shù)。本文章通過實(shí)例向大家講解array_walk_recursive函數(shù)的使用方法 2016-11-11
最新評(píng)論
做項(xiàng)目遇到個(gè)需求,需要對(duì)結(jié)果集中的數(shù)據(jù)進(jìn)行指定規(guī)則的順序排列。
例如,用戶狀態(tài)有四種:
=>未激活;1=>正常;2=>禁用;3=>軟刪除
現(xiàn)在的需求是,我要按照:正常->未激活->禁用->刪除;這個(gè)順序來進(jìn)行排序,同時(shí)按照注冊(cè)時(shí)間降序,網(wǎng)上查了很多資料,國(guó)內(nèi)提到這個(gè)的很少,在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); //追加額外參數(shù),例如搜索條件 $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 復(fù)雜應(yīng)用:
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排序?qū)嵗褪切【幏窒斫o大家的全部?jī)?nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
教你如何用php實(shí)現(xiàn)LOL數(shù)據(jù)遠(yuǎn)程獲取
LOL(英雄聯(lián)盟)最近非常的火爆,哥自然也在玩了,最近遇到個(gè)問題,就是每次想看看自己的戰(zhàn)斗力啥的,還得先開盒子等等,麻煩,最近有一個(gè)想法,打算把它實(shí)現(xiàn)出來。2014-06-06Laravel框架學(xué)習(xí)筆記(二)項(xiàng)目實(shí)戰(zhàn)之模型(Models)
上一篇已經(jīng)介紹開發(fā)環(huán)境的搭建,這篇將從項(xiàng)目實(shí)戰(zhàn)開發(fā),一步一步了解laravel框架。首先我們來了解下laravel框架的模型 (Models)2014-10-10PHP網(wǎng)頁游戲?qū)W習(xí)之Xnova(ogame)源碼解讀(一)
這篇文章主要介紹了PHP網(wǎng)頁游戲Xnova(ogame)源碼解讀,需要的朋友可以參考下2014-06-06php array_walk_recursive 使用自定的函數(shù)處理數(shù)組中的每一個(gè)元素
php中,如果需要對(duì)數(shù)組中的每個(gè)元素進(jìn)行一定規(guī)則的處理,我們可以使用array_walk_recursive,array_walk_recursive函數(shù)用于對(duì)數(shù)組中的每個(gè)成員遞歸地應(yīng)用用戶函數(shù)。本文章通過實(shí)例向大家講解array_walk_recursive函數(shù)的使用方法2016-11-11