How to execute raw queries with Laravel with using subquery

  laravel, laravel-query-builder, query-builder
select * FROM (SELECT o.customerID,o.slot,o.Mobile,o.orderNumber as orderNumber,o.Name,
                        o.acknowledge_status,o.customerAddress,
                        o.srType,s.startTime,s.endTime,DATE_FORMAT(r.roasterDate, '%d-%m-%Y')AS roasteDate,tlo.name 
                                                as oltname,o.portId,o.slaClock ,dop.podName,st.statusFFA as srStatus
                            from order o LEFT JOIN slot s ON s.slotId=o.slotId 
                                                Left join roast r ON o.roasterId=r.roasterId
                            LEFT JOIN engins e ON o.pengId=e.userId
                            LEFT JOIN tlo  ON tlo.deviceId=o.deviceId 
                                                    LEFT JOIN dop on dop.podCode=tlo.podCode
                        LEFT JOIN transition_status st ON st.statusReasonCRM=o.srStatus AND o.acknowledge_status=st.orderTransition                             where e.emailId='$emailId' AND o.status=1 AND o.slot='ERF' AND o.acknowledge_status=st.orderTransition
                        )t  GROUP BY orderNumber ORDER BY roasteDate  DESC

In tried to translate it to Laravel, with no success. So please can anyone suggest me the right way to solve my problem

Engineers::select(DB::raw('o.customerID,o.slot,o.Mobile,o.orderNumber as orderNumber,o.Name,
                            o.acknowledge_status,o.customerAddress,
                            o.srType,s.startTime,s.endTime,DATE_FORMAT(r.roasterDate, '%d-%m-%Y')AS roasteDate,tlo.name 
                                                    as oltname,o.portId,o.slaClock ,dop.podName,st.statusFFA as srStatus'))
            ->leftJoin('slot as s', 's.slotId', '=', 'o.slotId')
            ->leftJoin('roast as r', 'o.roasterId', '=', 'r.roasterId')
            ->leftJoin('engins as e', 'o.pengId', '=', 'e.userId')
            ->leftJoin('tlo as tlo', 'tlo.deviceId', '=', 'o.deviceId')
            ->leftJoin('dop as dop', 'dop.podCode', '=', 'tlo.podCode')
            ->leftJoin('transition_status as st', 'st.statusReasonCRM', '=', 'o.srStatus')
            ->Where('e.emailId','[email protected]')
            ->Where('o.status',1)
            ->Where('o.slot','ERF')
            ->Where('o.acknowledge_status','st.orderTransition')

Source: Laravel

Leave a Reply