时间戳

首页 > 解决方案 > 如何将 SQL 查询组连接到 laravel 查询中？

php - 如何将 SQL 查询组连接到 laravel 查询中？

问题描述

这是我在 SQL 中的查询。我需要转换成 Laravel 查询。因为这个查询在 Laravel 刀片上不受影响。所以我需要在 Laravel 查询中进行转换。谁能知道该怎么做？

Laravel 控制器

   class BookingDetailsController extends Controller
   {
       public function booking_list()
       {
           $role = Auth::user()->role;

           $data['page_title'] = "Booking Detail";
           $data['page_description'] = "Welcome to Admin Dashboard"; 
            $booking_detail = DB::select('SELECT bd.id as book_id, bd.recept_no, bd.advance_payment,bd.total_balance,
                               bd.is_booked, cc.name,  GROUP_CONCAT(dcb.rate SEPARATOR \'|\'), 
                               GROUP_CONCAT(dcb.pickup_point SEPARATOR \'|\'),  
                               GROUP_CONCAT(dcb.drop_point SEPARATOR \'|\'),  
                               GROUP_CONCAT(cn.car_number SEPARATOR \'|\') FROM booking_details bd
                               left join driver_car_booking_details dcb on bd.id = dcb.booking_id
                               left join car_nos cn on dcb.vehicle_id = cn.id
                               inner join customers cc on bd.customer_id = cc.id
                               group by bd.id
                               order by bd.id' ); 

           if($role == 'admin'){
               dd($booking_detail);
               return view('admin.booking_list')->with($data)
               ->with('booking_detail', $booking_detail);
           }
           if($role == 'oil_filling'){
               return view('oil_filling.booking_list')->with($data)
               ->with('booking_detail', $booking_detail);
           }else{
               return view('admin.booking_list')->with($data)
               ->with('booking_detail', $booking_detail);
           }

       }
}

原始查询：

    SELECT bd.id as book_id, bd.recept_no, bd.advance_payment,bd.total_balance,
            bd.is_booked, cc.name,  GROUP_CONCAT(dcb.rate SEPARATOR '|'), 
    GROUP_CONCAT(dcb.pickup_point SEPARATOR '|'),  
    GROUP_CONCAT(dcb.drop_point SEPARATOR '|'),  
    GROUP_CONCAT(cn.car_number SEPARATOR '|') FROM booking_details bd
            left join driver_car_booking_details dcb on bd.id = dcb.booking_id
            left join car_nos cn on dcb.vehicle_id = cn.id
            inner join customers cc on bd.customer_id = cc.id
            group by bd.id
            order by bd.id

标签： phplaravel

解决方案

原始查询构建器方法可用于在不使用 ORM 的情况下执行查询。

以下代码段将执行上述查询：

      $bookings = DB::select('SELECT bd.id as book_id, bd.recept_no, bd.advance_payment,bd.total_balance,
        bd.is_booked, cc.name,  GROUP_CONCAT(dcb.rate SEPARATOR \'|\'), 
GROUP_CONCAT(dcb.pickup_point SEPARATOR \'|\'),  
GROUP_CONCAT(dcb.drop_point SEPARATOR \'|\'),  
GROUP_CONCAT(cn.car_number SEPARATOR \'|\') FROM booking_details bd
        left join driver_car_booking_details dcb on bd.id = dcb.booking_id
        left join car_nos cn on dcb.vehicle_id = cn.id
        inner join customers cc on bd.customer_id = cc.id
        group by bd.id
        order by bd.id');

推荐阅读