select * from `eplan_vacancy` where `reference_id` in
(select `eplan_ep_details`.`reference_id`
from `eplan_ep_details`
inner join `eplan_court_cases` on `eplan_ep_details`.`reference_id` = `eplan_court_cases`.`reference_id`
where `ep_cc_status` = 1
group by `eplan_court_cases`.`reference_id`)
and `is_form_submitted` = 1 and `st_code` in ('U05', 'S13', 'S01')
group by `reference_id`
order by `created_at` desc
2 回答
慕容3067478
TA貢獻1773條經驗 獲得超3個贊
您可以使用閉包作為whereIn方法的第二個參數。
whereIn($column, $values, $boolean = 'and', $not = false)
$return = DB::table('eplan_vacancy')
->whereIn('reference_id', function ($query) {
return $query->select('eplan_ep_details.reference_id')
->from('eplan_ep_details')
->join('eplan_court_cases', 'eplan_ep_details.reference_id', '=', 'eplan_court_cases.reference_id')
->where('ep_cc_status', 1)
->groupBy('eplan_court_cases.reference_id');
})
->where('is_form_submitted', 1)
->whereIn('st_code', ['U05', 'S13', 'S01'])
->groupBy('reference_id')
->orderBy('created_at', 'desc');
dd($return->toSql());
結果
select * from `eplan_vacancy` where `reference_id` in
(
select `eplan_ep_details`.`reference_id`
from `eplan_ep_details`
inner join `eplan_court_cases` on `eplan_ep_details`.`reference_id` = `eplan_court_cases`.`reference_id`
where `ep_cc_status` = ?
group by `eplan_court_cases`.`reference_id`
)
and `is_form_submitted` = ?
and `st_code` in (?, ?, ?)
group by `reference_id`
order by `created_at` desc
慕容森
TA貢獻1853條經驗 獲得超18個贊
這是一個完整的 eloqent 查詢，您可以使用它來生成上述查詢，這里我使用了您需要根據您的模型名稱更改的模型。
EplanVacnacy：表“eplan_vacancy”的模型
$result = EplanVacnacy::whereIN('reference_id',function($q){
$q->from('eplan_ep_details')
->select('eplan_ep_details.reference_id')
->innerJoin('eplan_court_cases','eplan_ep_details.reference_id','=','eplan_court_cases.reference_id')
->where('eplan_ep_details.ep_cc_status',1)
->groupBy('eplan_court_cases.reference_id')
->get();
return $q;
})
->where('is_form_submitted',1)
->whereIN('st_code',['U05', 'S13', 'S01'])
->groupBy('reference_id')
->orderBy('created_at','desc')
->get();
- 2 回答
- 0 關注
- 219 瀏覽
添加回答
舉報
0/150
提交
取消