不同的SQL查询场景在不同的框架里都有各自的风格来快速实现,YII里用的是 new CDbCriteria的方式来组装sql,Laravel有when方法来判断满足
源码路径:vendor\laravel\framework\src\Illuminate\Database\Concerns\BuildsQueries.php,分析源码可以得出:when会判断第一个参数的真与假,如果是真,则执行第一个callback,如果是假,则执行默认的方法
/** * Apply the callback's query changes if the given "value" is true. * * @param mixed $value * @param callable $callback * @param callable $default * @return mixed */ public function when($value, $callback, $default = null) { if ($value) { return $callback($this, $value) ?: $this; } elseif ($default) { return $default($this, $value) ?: $this; } return $this; }
举例一(if else )
$data = $obj->DB::connection('mysql')->table('test') ->where('membership_openid', '=', $wxOpenId) ->where('start_time', '>=', $start_time) ->groupBy('business_name','business_id') ->when($params['type'] == 1,function ($query){ $query->orderBy('account', 'desc')->orderBy('times', 'desc');//如果成立 },function ($query){ $query->orderBy('times', 'desc')->orderBy('account', 'desc');//否则 }) ->limit(5) ->get();
举例二(use )
$data = DB::connection('mysql_snj_paike')->table('class_stage_subject_teacher as a') ->leftJoin('order_class_stage_subject as b', function($join){ $join->on('a.class_id', '=', 'b.class_id') ->on('a.stage_id', '=', 'b.stage_id') ->on('a.subject_id', '=', 'b.subject_id') ->on('a.batch_num', '=', 'b.batch_num'); }) ->where('a.ID', '=', $csstId) ->Join('order_info as c', 'b.order_no', '=', 'c.order_no') ->select(['a.id as object_id', 'a.TEACHER_STAFF_NO as staff_no', 'c.STUDENT_ID as student_id']) ->where('c.ORDER_STATE', '<>', 7) ->groupBy('a.id', 'a.TEACHER_STAFF_NO', 'c.STUDENT_ID') ->when($pageInfo,function ($query) use ($pageInfo){ //use 这里使用的是一个数组变量 $query->limit($pageInfo['pageSize'])->offset(($pageInfo['pageNum']-1)*$pageInfo['pageSize']); }) ->orderBy('a.id', 'DESC') ->get() ->toArray();