laravel 实现多条件查询

有人问说 ‘ laravel 的where 如何改进使其适应多条件？ 感觉laravel的where语句好弱’。其实那并不是这样的，原因是laravel中数据库where查询的玩法和其他框架如ThinkPHP有所不同，下面我就用一个实例来说明吧，大家请看

DB用法：

$list = DB::table($this->table)  
->join('user', 'user.id', '=', $this->table . '.user_id')  
->where(function($query) use($data) {  
    if (!empty($data['order_sn'])) {  
        $query->where($this->table . '.order_sn', 'like', '%' . $data['order_sn'] . '%');  
    }  
})  
->where(function($query) use($data) {  
    if (isset($data['order_status']) && is_numeric($data['order_status'])) {  
        $query->where($this->table . '.order_status', '=', $data['order_status']);  
    }  
})  
->where(function($query) use($data) {  
    if (isset($data['ship_status']) && is_numeric($data['ship_status'])) {  
        $query->where($this->table . '.ship_status', '=', $data['ship_status']);  
    }  
})  
->where(function($query) use($data) {  
    if (isset($data['pay_status']) && is_numeric($data['pay_status'])) {  
        $query->where($this->table . '.pay_status', '=', $data['pay_status']);  
    }  
})  
->where(function($query) use($data) {  
    if (!empty($data['start_time']) && !empty($data['end_time'])) {  
        $query->where($this->table . '.update_time', '>=', $data['start_time'])  
    ->where($this->table . '.update_time', '<=', $data['end_time']);  
    } else if (!empty($data['start_time'])) {  
        $query->where($this->table . '.update_time', '>=', $data['start_time']);  
    } else if (!empty($data['end_time'])) {  
        $query->where($this->table . '.update_time', '<=', $data['end_time']);  
    }  
})  
->where($this->table . '.drugstore_id', '=', 0)  
->where($this->table . '.user_id', '!=', 0)  
->select($this->table . '.id as id',   
    $this->table . '.order_sn as order_sn',   
    $this->table . '.order_status as order_status',   
    $this->table . '.ship_status as ship_status',   
    $this->table . '.pay_status as pay_status',   
    $this->table . '.total_paid as total_paid',   
    $this->table . '.update_time as update_time',   
    'user.nickname as name',   
    'user.mobile as phone',   
    $this->table . '.is_remittance as is_remittance')  
->orderBy($sortby, $sortvalue)  
->take($data['pageSize'])  
->skip($data['pageNo'] > 1 ? ($data['pageNo'] - 1) * $data['pageSize'] : 0)  
->get();

Eloquent用法

$requests = $requests;  
           $orders = Order::select()->with('has_orderitems')->where('status','>=',config('payment.ORDER_STATE_PAY'))->where(function($query) use($requests) {  
               if (isset($requests['school_name']) && $requests['school_name']) {  
                   $query->where('sid', '=', $requests['school_name']);  
               }  
           })->where(function($query) use($requests) {  
               if (isset($requests['dorm_name']) && $requests['dorm_name']) {  
                   $query->whereIn('did', $requests['dorm_name']);  
               }  
           })->where(function($query) use($requests) {  
               if (!empty($requests['date_time_start']) && !empty($requests['date_time_end'])) {  
                   $query->where('created_at', '>=', $requests['date_time_start'])  
               ->where( 'created_at', '<=', $requests['date_time_start']);  
               } else if (!empty($data['date_time_start'])) {  
                   $query->where('created_at', '>=', $requests['date_time_start']);  
               } else if (!empty($data['date_time_end'])) {  
                   $query->where('created_at', '<=', $requests['date_time_start']);  
               }  
           })->get();  

附：use, php5.3以后的一个新鲜的家伙…  使用场景：闭包的内部函数使用外部函数中定义的变量.    用use来使用闭包外部定义的变量的.