CODEWITHSUNDEEP
phpmysqllaravellaravel-query-builder
salah1337
salah1337

Reputation: 67

mysql query to laravel orm

My goal is to convert the following SQL query to to Laravel query builder syntax,

    $qstA = "
            select sum(tmpbutbldata.Personnel_Hours_Spent) as PHS from tmpbutbldata 
            left join tbl_testlocation_links on tmpbutbldata.Test_Request_Number= tbl_testlocation_links.Test_Request_number 
            where 
            tmpbutbldata.Date_Test_Completed between '".$dateBack."' and '".$dateCurr."' 
            and tbl_testlocation_links.".$Location."='1' 
            and tmpbutbldata.type = '1' 
            and tmpbutbldata.cancelled = '0' 
            or 
            tmpbutbldata.Date_Test_Completed between '".$dateBack."' and '".$dateCurr."' 
            and tbl_testlocation_links.".$Location."='1' 
            and tmpbutbldata.type = '2' 
            and tmpbutbldata.cancelled = '0'"
    ;

what i've tried:

DB::table("tbldata")
        ->select(DB::raw('*'))
        ->leftJoin('tbl_testlocation_links', 'tbldata.Test_Request_Number', '=', 'tbl_testlocation_links.Test_Request_number')
        ->where(['Date_Test_Completed', '<', $dateBack],
                ['Date_Test_Completed', '>', $dateCurr],
                ['tbl_testlocation_links'.$location->TestLocation, '=', 1],
                ['type', '=', 1],
                ['cancelled', '=', 0])
        ->orWhere(['Date_Test_Completed', '<', $dateBack],
                ['Date_Test_Completed', '>', $dateCurr],
                ['tbl_testlocation_links'.$location->TestLocation, '=', 1],
                ['type', '=', 2],
                ['cancelled', '=', 0])
        ->get();

But I get the following error ErrorException Array to string conversion

Upvotes: 0

Views: 52

Answers (1)

Donkarnash
Donkarnash

Reputation: 12835

Try

DB::table("tbldata")
        ->select(DB::raw('*'))
        ->leftJoin('tbl_testlocation_links', 'tbldata.Test_Request_Number', '=', 'tbl_testlocation_links.Test_Request_number')
        ->where([
             ['Date_Test_Completed', '<', $dateBack],
             ['Date_Test_Completed', '>', $dateCurr],
             ['tbl_testlocation_links'.$location->TestLocation, '=', 1],
             ['type', '=', 1],
             ['cancelled', '=', 0]
        ])
        ->orWhere([
             ['Date_Test_Completed', '<', $dateBack],
             ['Date_Test_Completed', '>', $dateCurr],
             ['tbl_testlocation_links'.$location->TestLocation, '=', 1],
             ['type', '=', 2],
             ['cancelled', '=', 0]
        ])
        ->get();

Multiple conditions must be passed in a single where clause as an array of arrays, where each nested array represents a condition

Upvotes: 1

Related Questions