我有一個查詢，涉及選擇多列的最新日期/值。我使用了mysql的greatest功能。我的問題是如何在已經有 select 陳述句的查詢構建器中使用或插入它。
這是我試過的查詢
$ses = DB::table('ses_contracts')
->join('ses_worker','ses_contracts.id','ses_worker.ses_id')
->join('corporates','corporates.id','ses_contracts.outsourcer')
->select([
'corporates.corpname as outsourcer',
'ses_contracts.outsourcer_manager_name as manager_name',
'ses_worker.worker_name as worker_name',
'ses_worker.sesnum1 as sesnum1',
'ses_worker.sesnum2 as sesnum2',
'ses_contracts.work_date1 as start',
// this is the one i want the max_date for the 6 columns.
DB::raw('SELECT GREATEST (work_date1,work_date2,work_date3,work_date4,work_date5,work_date6) as end from ses_contracts')
// end
])
->get();
die($ses);
部分結果是這個
{
"outsourcer": "㈱daOn",
"manager_name": "黃",
"worker_name": "4",
"sesnum1": "MS-JAN-",
"sesnum2": 1,
"start": "2021-10-29"
}
我想要的是
{
"outsourcer": "㈱daOn",
"manager_name": "黃",
"worker_name": "4",
"sesnum1": "MS-JAN-",
"sesnum2": 1,
"start": "2021-10-29",
"end": "2021-10-30" // supposed oct 30 is latest date of the 6 columns
}
當我在 mysql 或DB::select陳述句中嘗試查詢時。有用。我只是不知道如何將它添加到現有的選擇中。希望有人能幫忙。謝謝你們。
uj5u.com熱心網友回復：
使用selectRaw.
$ses = DB::table('ses_contracts')
->join('ses_worker', 'ses_contracts.id', 'ses_worker.ses_id')
->join('corporates', 'corporates.id', 'ses_contracts.outsourcer')
->select([
'corporates.corpname as outsourcer',
'ses_contracts.outsourcer_manager_name as manager_name',
'ses_worker.worker_name as worker_name',
'ses_worker.sesnum1 as sesnum1',
'ses_worker.sesnum2 as sesnum2',
'ses_contracts.work_date1 as start'
])
->selectRaw('GREATEST(ses_contracts.work_date1, ses_contracts.work_date2, ses_contracts.work_date3, ses_contracts.work_date4, ses_contracts.work_date5, ses_contracts.work_date6) as end')
->get();
https://laravel.com/docs/8.x/queries#raw-methods
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/317435.html
上一篇：作曲家需要findbrok/php-watson-api-bridge給出這些錯誤如何解決這個我的laravel是8