Laravel querybuilder select array with raw query

  laravel, mysql, php

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.

Source: Ask PHP

LEAVE A COMMENT