現在我的業務場景是一個非常複雜的搜尋，要求是先顯示本公司的人員，後面顯示其他公司的人員
service 方法程式碼如下
/**
* @param $params
* @param array $where
* @return Illuminate\Contracts\Pagination\LengthAwarePaginator
*/
public function getAuntList($params,$where = [],$sortArr = [])
{
$page_row = $params['page_row'] ?? 8;
$uid = $params['user_id']??0;
$model = WechatUser::query();
$model->from('wechat_user');
$model->join('jz_user_base as jub', 'jub.user_id', '=', 'wechat_user.id');
$model->join('jz_user_detail as jud', 'jud.user_id', '=', 'wechat_user.id');
// 已簽約符合規則的
if(isset($params['work_date']) && $params['work_date']!=''){
$model->join('admin_contract as a_contract','a_contract.aunt_id','=','wechat_user.id');
$model->whereIn('a_contract.status',[1,2]);
$model->where('a_contract.small_start_time','>=',strtotime($params['work_date']));
$model->orWhere('a_contract.large_end_time','<=',strtotime($params['work_date']));
$model->groupBy(['a_contract.aunt_id']);
}
// 專業限制
if(isset($params['service_crowd']) && $params['service_crowd']!=''){
//只有一級 聯合查詢需要加 groupBy
$model->join('admin_train as a_train','a_train.user_id','=','wechat_user.id');
$model->where('a_train.status','=',3);//培訓結束的
$model->where('a_train.service_crowd','=',$params['service_crowd']);
$model->groupBy(['a_train.user_id']);
} elseif((isset($params['service_crowd']) && $params['service_crowd']!=0) && (isset($params['service_items']) && $params['service_items']!=0)) {
// 不需要加groupBy
$model->join('admin_train as a_train','a_train.user_id','=','wechat_user.id');
$model->where('a_train.status','=',3);//培訓結束的
$model->where('a_train.service_items','=',$params['service_items']);
$model->groupBy(['a_train.user_id']);
}
$model->where($where);
$model->whereIn('jub.is_match', [2, 5]);//status 5 是自主註冊成為保姆 2是後臺新增設定為可匹配狀態
$model->whereIn('jub.aunt_status', [3,4]);// 阿姨狀態 3,4 都可以被查詢到 待崗 | 已簽約
if (!empty($sortArr)) {
foreach ($sortArr as $k => $val) {
$model->orderBy($k, $val);
}
}
$model->select(['wechat_user.id','jub.work_type', 'jub.aunt_status','jub.jz_company_id', 'jub.realname', 'jub.header_pic', 'jub.month_money', 'jud.age', 'jud.has_exp', 'jud.speciality', 'jud.native_province', 'jud.education']);
// 若是登入狀態 則先顯示本公司的保姆 否則 沒有公司id條件無需處理 也就無需union 查詢
if($uid > 0) {
//其他公司
$query1 = WechatUser::query();
$query1->from('wechat_user');
$query1->join('jz_user_base as jub', 'jub.user_id', '=', 'wechat_user.id');
$query1->join('jz_user_detail as jud', 'jud.user_id', '=', 'wechat_user.id');
// 到崗時間限制
if(isset($params['work_date']) && $params['work_date']!=''){
$query1->join('admin_contract as a_contract','a_contract.aunt_id','=','wechat_user.id');
$query1->whereIn('a_contract.status',[1,2]);
$query1->where('a_contract.small_start_time','>=',strtotime($params['work_date']));
$query1->orWhere('a_contract.large_end_time','<=',strtotime($params['work_date']));
$query1->groupBy(['a_contract.aunt_id']);
}
// 專業限制
if(isset($params['service_crowd']) && $params['service_crowd']!=''){
//只有一級 聯合查詢需要加 groupBy
$query1->join('admin_train as a_train','a_train.user_id','=','wechat_user.id');
$query1->where('a_train.status','=',3);//培訓結束的
$query1->where('a_train.service_crowd','=',$params['service_crowd']);
$query1->groupBy(['a_train.user_id']);
} elseif((isset($params['service_crowd']) && $params['service_crowd']!=0) && (isset($params['service_items']) && $params['service_items']!=0)) {
// 不需要加groupBy
$query1->join('admin_train as a_train','a_train.user_id','=','wechat_user.id');
$query1->where('a_train.status','=',3);//培訓結束的
$query1->where('a_train.service_items','=',$params['service_items']);
}
$whereFirst = $where[0];
unset($where[0]);
array_push($where, ['jz_company_id', '!=', $whereFirst[2]]);
$query1->where($where);
$query1->whereIn('jub.is_match', [2,5]);//status 5 是自主註冊成為保姆 2是後臺新增設定為可匹配狀態
$query1->whereIn('jub.aunt_status', [3,4]);// 阿姨狀態 3,4 都可以被查詢到 待崗 | 已簽約
if (!empty($sortArr)) {
foreach ($sortArr as $k => $val) {
$query1->orderBy($k, $val);
}
}
$query1->select(['wechat_user.id','jub.work_type','jub.aunt_status','jub.jz_company_id', 'jub.realname', 'jub.header_pic', 'jub.month_money', 'jud.age', 'jud.has_exp', 'jud.speciality', 'jud.native_province', 'jud.education']);
//最後分頁
$model->union($query1);
}
return $model->paginate($page_row);
}列印的sql如下
array:2 [
0 => array:3 [
"query" => "select count(*) as aggregate from ((select `wechat_user`.`id`, `jub`.`work_type`, `jub`.`aunt_status`, `jub`.`jz_company_id`, `jub`.`realname`, `jub`.`header_pic`, `jub`.`month_money`, `jud`.`age`, `jud`.`has_exp`, `jud`.`speciality`, `jud`.`native_province`, `jud`.`education` from `wechat_user` inner join `jz_user_base` as `jub` on `jub`.`user_id` = `wechat_user`.`id` inner join `jz_user_detail` as `jud` on `jud`.`user_id` = `wechat_user`.`id` inner join `admin_contract` as `a_contract` on `a_contract`.`aunt_id` = `wechat_user`.`id` where (`a_contract`.`status` in (?, ?) and `a_contract`.`small_start_time` >= ? or `a_contract`.`large_end_time` <= ? and (`jub`.`jz_company_id` = ? and `jub`.`work_addr_city` = ? and `jub`.`is_check` = ? and `jub`.`realname` like ?) and `jub`.`is_match` in (?, ?) and `jub`.`aunt_status` in (?, ?)) and `wechat_user`.`is_del` = ? group by `a_contract`.`aunt_id`) union (select `wechat_user`.`id`, `jub`.`work_type`, `jub`.`aunt_status`, `jub`.`jz_company_id`, `jub`.`realname`, `jub`.`header_pic`, `jub`.`month_money`, `jud`.`age`, `jud`.`has_exp`, `jud`.`speciality`, `jud`.`native_province`, `jud`.`education` from `wechat_user` inner join `jz_user_base` as `jub` on `jub`.`user_id` = `wechat_user`.`id` inner join `jz_user_detail` as `jud` on `jud`.`user_id` = `wechat_user`.`id` inner join `admin_contract` as `a_contract` on `a_contract`.`aunt_id` = `wechat_user`.`id` where (`a_contract`.`status` in (?, ?) and `a_contract`.`small_start_time` >= ? or `a_contract`.`large_end_time` <= ? and (`jub`.`work_addr_city` = ? and `jub`.`is_check` = ? and `jub`.`realname` like ? and `jz_company_id` != ?) and `jub`.`is_match` in (?, ?) and `jub`.`aunt_status` in (?, ?)) and `wechat_user`.`is_del` = ? group by `a_contract`.`aunt_id`)) as `temp_table`"
"bindings" => array:26 [
0 => 1
1 => 2
2 => 1575907200
3 => 1575907200
4 => 16
5 => 1101
6 => 2
7 => "%aa%"
8 => 2
9 => 5
10 => 3
11 => 4
12 => 1
13 => 1
14 => 2
15 => 1575907200
16 => 1575907200
17 => 1101
18 => 2
19 => "%aa%"
20 => 16
21 => 2
22 => 5
23 => 3
24 => 4
25 => 1
]
"time" => 23.96
]
1 => array:3 [
"query" => "(select `wechat_user`.`id`, `jub`.`work_type`, `jub`.`aunt_status`, `jub`.`jz_company_id`, `jub`.`realname`, `jub`.`header_pic`, `jub`.`month_money`, `jud`.`age`, `jud`.`has_exp`, `jud`.`speciality`, `jud`.`native_province`, `jud`.`education` from `wechat_user` inner join `jz_user_base` as `jub` on `jub`.`user_id` = `wechat_user`.`id` inner join `jz_user_detail` as `jud` on `jud`.`user_id` = `wechat_user`.`id` inner join `admin_contract` as `a_contract` on `a_contract`.`aunt_id` = `wechat_user`.`id` where (`a_contract`.`status` in (?, ?) and `a_contract`.`small_start_time` >= ? or `a_contract`.`large_end_time` <= ? and (`jub`.`jz_company_id` = ? and `jub`.`work_addr_city` = ? and `jub`.`is_check` = ? and `jub`.`realname` like ?) and `jub`.`is_match` in (?, ?) and `jub`.`aunt_status` in (?, ?)) and `wechat_user`.`is_del` = ? group by `a_contract`.`aunt_id`) union (select `wechat_user`.`id`, `jub`.`work_type`, `jub`.`aunt_status`, `jub`.`jz_company_id`, `jub`.`realname`, `jub`.`header_pic`, `jub`.`month_money`, `jud`.`age`, `jud`.`has_exp`, `jud`.`speciality`, `jud`.`native_province`, `jud`.`education` from `wechat_user` inner join `jz_user_base` as `jub` on `jub`.`user_id` = `wechat_user`.`id` inner join `jz_user_detail` as `jud` on `jud`.`user_id` = `wechat_user`.`id` inner join `admin_contract` as `a_contract` on `a_contract`.`aunt_id` = `wechat_user`.`id` where (`a_contract`.`status` in (?, ?) and `a_contract`.`small_start_time` >= ? or `a_contract`.`large_end_time` <= ? and (`jub`.`work_addr_city` = ? and `jub`.`is_check` = ? and `jub`.`realname` like ? and `jz_company_id` != ?) and `jub`.`is_match` in (?, ?) and `jub`.`aunt_status` in (?, ?)) and `wechat_user`.`is_del` = ? group by `a_contract`.`aunt_id`) limit 5 offset 0"
"bindings" => array:26 [
0 => 1
1 => 2
2 => 1575907200
3 => 1575907200
4 => 16
5 => 1101
6 => 2
7 => "%aa%"
8 => 2
9 => 5
10 => 3
11 => 4
12 => 1
13 => 1
14 => 2
15 => 1575907200
16 => 1575907200
17 => 1101
18 => 2
19 => "%aa%"
20 => 16
21 => 2
22 => 5
23 => 3
24 => 4
25 => 1
]
"time" => 23.13
]
]查詢到的資料結果如下：
{
"success": true,
"error_code": 200,
"message": "success",
"data": {
"total": 1,
"last_page": 1,
"current_page": 1,
"data": [
{
"id": 118,
"work_type": "月嫂",
"aunt_status": 2,
"jz_company_id": 16,
"realname": "該喝喝",
"header_pic": "60456dd4169f1f64beb2e447f49d7acf9839.jpg",
"month_money": 1043,
"age": 21,
"has_exp": 2,
"speciality": ",6,10,14,",
"native_province": "北京人",
"education": "小學",
"isFav": 0,
"spArr": []
},
{
"id": 60,
"work_type": "月嫂",
"aunt_status": 4,
"jz_company_id": 17,
"realname": "aaa",
"header_pic": "20191206/83057e6e4f63cd287de5a7ef81cdc2a3.png",
"month_money": 3501,
"age": 28,
"has_exp": 5,
"speciality": "101,102,103,",
"native_province": "北京人",
"education": "小學",
"isFav": 0,
"spArr": [
{
"name": "善於溝通"
},
{
"name": "有愛心"
},
{
"name": "誠信服務"
}
]
},
{
"id": 61,
"work_type": "月嫂",
"aunt_status": 3,
"jz_company_id": 17,
"realname": "aaaa",
"header_pic": "20191206/83057e6e4f63cd287de5a7ef81cdc2a3.png",
"month_money": 3502,
"age": 29,
"has_exp": 6,
"speciality": "104,105,106,",
"native_province": "天津人",
"education": "小學",
"isFav": 0,
"spArr": [
{
"name": "勤勞善良"
},
{
"name": "乾淨利索"
},
{
"name": "收納整理"
}
]
}
]
}
}查詢關鍵字 aa 但是結果 和預期的不一樣 這是為什麼呢 ？ 是不是where條件有優先順序相關的限制呢 ？求大神指點