I have a query that involves selecting the latest date/value of multiple columns. I used mysql's greatest function. My question is how can i use or insert it in a querybuilder that has already a select statement.
Here is the query I tried
$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);
The partial result is this one
{
"outsourcer": "㈱daOn",
"manager_name": "黄",
"worker_name": "4",
"sesnum1": "MS-JAN-",
"sesnum2": 1,
"start": "2021-10-29"
}
What I want is
{
"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
}
When I try the query in mysql or in DB::select statement. It works. I just dont know how to add it in an existing select. Hope someone can help. Thanks guys.
CodePudding user response：
Use 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();