laravel怎么优雅的拼接where，处理whereIn与where数组查询的问题

Question

现在我要处理多条件搜索的问题

我的代码是这么处理的

  switch ($where['type']) {
                //基层医院姓名
                case 1:
                    $condition [] = ['create_doctor_hospital', 'like', $where['name'] . "%"];
                    break;
                //基层医生姓名
                case 2:
                    $condition [] = ['create_doctor_name', 'like', $where['name'] . "%"];
                    break;
                //上级医生姓名
                case 3:
                    $doctor_model=UserModel::select('doctoruid')
                        ->where('doctorname', 'like', $where['name'] . "%")
                        ->get('doctoruid');
                    //todo:会存在匹配到多个叫刘江淮的医生 结果集要怎么处理
                    if(empty($doctor_model)){
                        break;
                    }else{
                        foreach ($doctor_model as $doc){
                            $check_doctor_id[]=$doc->doctoruid;
                        }
                    }
                    $condition[] =['check_doctor_uid','in',$check_doctor_id];
                    break;
            }
        }


       if (isset($where['start_time']) && isset($where['end_time'])) {
            $order_data = $this
                ->join(HDW_CHECKREPORT_ECG, $this->table . '.', '=', HDW_CHECKREPORT_ECG . '.id')
                ->select(HDW_CHECKREPORT_ECG . '.check_time', HDW_CHECKREPORT_ECG . '.patient_name', HDW_CHECKREPORT_ECG . '.patient_mobile', HDW_CHECKREPORT_ECG . '.patient_idcard', $this->table . '.orderid', $this->table . '.pay_money')
                ->whereBetween(HDW_ORDER . '.created_at', [$where['start_time'], $where['end_time'] + 24 * 60 * 60])
                ->where($condition)
                ->get();
        } else {
            //todo:sql待测试
            $order_data = $this
                ->join('doctor_user', $this->table . '.check_doctor_uid', '=', 'doctor_user.doctoruid')
                ->join(HDW_PRODUCT, $this->table . '.product_id', '=', HDW_PRODUCT . '.productid')
                ->select($this->table . '.orderid', $this->table . '.patient_name', $this->table . '.product_id', $this->table . '.create_doctor_uid', $this->table . '.pay_money', $this->table . '.create_doctor_hospital', $this->table . '.check_doctor_uid', $this->table . '.create_doctor_name', 'doctor_user.doctorname', HDW_PRODUCT . '.name')
                ->where($condition)
                ->get();
        }
        
       由于前几个条件都是拼接数组 作为where条件传入的 在搜索上级医生的时候 由于会匹配到多个医生 所以需要使用到wherein   但是不知道怎么去拼接whereIn  或者  作为数组传进去  求sg大神解惑

Answer 1

你的代码有错误：

//in查询应该用whereIn
$condition[] =['check_doctor_uid','in',$check_doctor_id]; // 错误
// Illuminate\Database\Query\Builder关于operators定义中，并没有in
public $operators = [
    '=', '<', '>', '<=', '>=', '<>', '!=',
    'like', 'like binary', 'not like', 'between', 'ilike',
    '&', '|', '^', '<<', '>>',
    'rlike', 'regexp', 'not regexp',
    '~', '~*', '!~', '!~*', 'similar to',
    'not similar to', 'not ilike', '~~*', '!~~*',
];


//->where($condition) 这种写法有问题

链式调用也可以不一次写完的，参考代码

$query = DB::table('orders')->select('department', DB::raw('SUM(price) as total_sales'));
if(...){
    $query = ...
}
$query = $query->groupBy('department')->havingRaw('SUM(price) > 2500');
$orders = $query->get();

如果是5.4版本，可以使用when方法，参考https://segmentfault.com/a/11...

Answer 2

不知道这样算不算优雅

$query = Mission::select(DB::raw('IFNULL(user_mission_log.id, 0) as id'), 'mission.id as mission_id', 'mission.type as mission_type', 'mission.name as mission_name', 'description', DB::raw('IFNULL(current_process, 0) as current_process'), DB::raw('IFNULL(user_mission_log.total_process,mission.num) as total_process'), DB::raw(sprintf('IFNULL(user_mission_log.status, %d) as status', UserMissionLog::STATUS_UNFINISHED)), DB::raw(sprintf('IFNULL(user_mission_log.is_settle, %d) as is_settle', UserMissionLog::SETTLE_STATUS_NOT_YET)), DB::raw('IFNULL(mission_award.gold, 0) as gold'), DB::raw('IFNULL(mission_award.score, 0) as score'), 'mission.description')
            ->leftJoin('user_mission_log', function ($join) use ($userId) {
                $join->on('mission.id', '=', 'user_mission_log.mission_id')
                    ->where('user_mission_log.user_id', '=', $userId)
                    ->where(function ($where) {
                        $where->where(function ($where) {
                            $where->where('user_mission_log.create_time', '>=', Carbon::today()->toDateTimeString())
                                ->where('user_mission_log.create_time', '<', Carbon::today()->tomorrow())
                                ->where('mission.type', '=', Mission::TYPE_DAILY);
                        })
                            ->orWhere('mission.type', '!=', Mission::TYPE_DAILY);
                    });
            })
            ->leftJoin('mission_award', 'mission_award.mission_id', '=', 'mission.id')
            ->where('mission_award.is_del', '=', MissionAward::IS_NOT)
            ->where('mission.is_del', '=', Mission::IS_NOT)
            ->where('mission.status', '=', Mission::STATUS_OPEN)
            ->where(function ($where) {
                $where->whereIn(
                    'mission.id',
                    UserMissionLog::select('mission_id')
                        ->where('user_id', '=', 0)
                        ->where('create_time', '>=', Carbon::today()->toDateTimeString())
                        ->where('create_time', '<', Carbon::today()->tomorrow())
                        ->get()->toArray()
                )
                    ->orWhere('mission.type', '!=', Mission::TYPE_DAILY);
            })
            ->where(function ($where) {
                $where->where(function ($where) {
                    $where->where('mission.type', '=', Mission::TYPE_ACT)
                        ->where(function ($where) {
                            $where->where('user_mission_log.is_settle', '=', UserMissionLog::SETTLE_NOT_YET)
                                ->orWhere(function ($where) {
                                    $where->where('user_mission_log.is_settle', '=', UserMissionLog::SETTLE_DONE)
                                        ->where('user_mission_log.settle_time', '>=', Carbon::today()->toDateTimeString())
                                        ->where('user_mission_log.settle_time', '<', Carbon::tomorrow()->toDateTimeString());
                                });
                        });
                })
                    ->orWhere(function ($where) {
                        $where->where('mission.start', '<=', Carbon::now()->toDateTimeString())
                            ->where('mission.end', '>', Carbon::now()->toDateTimeString());
                    });
            })
            ->where(function ($where) use ($type) {
                $type === null ?: $where->where('mission.type', '=', $type);
            })
            ->where(function ($where) {
                $where->WhereRaw(sprintf(
                    '(
                        (mission.type = %d and ((user_mission_log.create_time >= "%s" and user_mission_log.create_time < "%s") or (user_mission_log.create_time is null) ))
                         or 
                        (mission.type = %d)
                         or 
                        (mission.type = %d and ( (user_mission_log.is_settle = %d or user_mission_log.is_settle is null) or (user_mission_log.create_time >= "%s" and user_mission_log.create_time < "%s") ) )
                     )',
                    Mission::TYPE_DAILY,
                    Carbon::today()->toDateTimeString(),
                    Carbon::tomorrow()->toDateTimeString(),
                    Mission::TYPE_ACT,
                    Mission::TYPE_NEW,
                    UserMissionLog::SETTLE_NOT_YET,
                    Carbon::today()->toDateTimeString(),
                    Carbon::tomorrow()->toDateTimeString()
                ));
            })
            ->orderByRaw(sprintf('FIELD(mission.type, %d, %d, %d)', Mission::TYPE_ACT, Mission::TYPE_DAILY, Mission::TYPE_NEW))
            ->orderByRaw('current_process/total_process desc')
            ->orderBy('mission.id', 'asc');

        $total = $query->count();
        $data = $query->offset($offset)->limit($limit)->get()->toArray();

Answer 3

多谢了 明白了

Answer 4

改了一下, 类似这样, 我觉得更好.

 // 这
$query = Order::query();

switch ($where['type']) {
    //基层医院姓名
    case 1:
        // 这
        $query->where('create_doctor_hospital', 'like', $where['name'] . "%");
        break;
        //基层医生姓名
    case 2:
        // 和1一样
        break;
    case 3:
        $doctor_model=UserModel::select('doctoruid')
        ->where('doctorname', 'like', $where['name'] . "%");

        // 这
        $query->whereIn('check_doctor_uid', $doctor_model);
        break;
}


if (isset($where['start_time']) && isset($where['end_time'])) {
    $query->join(HDW_CHECKREPORT_ECG, $this->table . '.', '=', HDW_CHECKREPORT_ECG . '.id')
        ->select([
        HDW_CHECKREPORT_ECG . '.check_time',
        HDW_CHECKREPORT_ECG . '.patient_name',
        HDW_CHECKREPORT_ECG . '.patient_mobile',
        HDW_CHECKREPORT_ECG . '.patient_idcard',
        $this->table . '.orderid',
        $this->table . '.pay_money'
    ])
    ->whereBetween(HDW_ORDER . '.created_at', [
        $where['start_time'],
        $where['end_time'] + 24 * 60 * 60
    ]);
} else {
    $query->join('doctor_user', $this->table . '.check_doctor_uid', '=', 'doctor_user.doctoruid')
    ->join(HDW_PRODUCT, $this->table . '.product_id', '=', HDW_PRODUCT . '.productid')
        ->select([
        $this->table . '.orderid',
        $this->table . '.patient_name',
        $this->table . '.product_id',
        $this->table . '.create_doctor_uid',
        $this->table . '.pay_money',
        $this->table . '.create_doctor_hospital',
        $this->table . '.check_doctor_uid',
        $this->table . '.create_doctor_name',
        'doctor_user.doctorname',
        HDW_PRODUCT . '.name'
    ]);
}

// 这
$order_data = $query->get();