How to convert this Query into Laravel Eloquent or Query Builder way?

@lia97

try this

$data = DB::table("STOCK_MATERIAL AS x")
        
        ->select('x.MATERIAL_ID', 'STOCK_MATERIAL_EPI.HOUSE_NO', 'x.TXN_DATE', 'STOCK_MATERIAL_EPI.PROGRAM_NAME', 'PUR_EPISODE_HDR.EPI_NO', 'x.REMARKS', 'x.LOCATION_ID as shelf_no', 'stock_material_slag.remarks as short_list', 

		DB::raw("(SELECT TAPET_NAME FROM MA_TAPE_TYPE WHERE TAPET_CODE = x.MATERIAL_TYPE) as media_type"),

		DB::raw("(SELECT TAPEF_NAME FROM MA_TAPE_FORMAT WHERE TAPEF_CODE = x.MATERIAL_FORMAT) as media_format"),

		DB::raw("(SELECT MAX (last_date) FROM run_master WHERE run_master.row_id_epi = PUR_EPISODE_HDR.row_id AND run_master.run_aired = 'Y') as last_tx,"),
	            
	    DB::raw('CASE 	WHEN x.iden_flag = 'P' THEN STOCK_MATERIAL_EPI.epi_title 	WHEN x.iden_flag = 'C' THEN STOCK_MATERIAL_EPI.prod_version_name 	WHEN x.iden_flag = 'M' THEN STOCK_MATERIAL_EPI.promo_name  END as episode_title'))


->leftJoin('STOCK_MATERIAL_EPI', 'x.MATERIAL_ID', '=', 'STOCK_MATERIAL_EPI.MATERIAL_ID')
->leftJoin('stock_material_slag', 'x.MATERIAL_ID', '=', 'stock_material_slag.MATERIAL_ID')
->leftJoin('PUR_EPISODE_HDR', 'STOCK_MATERIAL_EPI.ROW_ID_EPI', '=', 'PUR_EPISODE_HDR.ROW_ID')
->get();

I'd say if it works use as is with db facade, see https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

It's a good example of using db facade.