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