将原始SQL转换为使用Eloquent Query Builder

Question

如何将以下复杂的SQL查询转换为使用Eloquent查询生成器?我想使用join()、where()、get()等方法

下面的查询返回位置列表以及已兑换的代金券的计数。

select 
        a.location_name,
        '' as dates,
        a.places,
        sum(netvalue155), 
        sum(netvalue135) from
        (
            select 
                l.id,
                l.location_name,
                b.places,
                case when v.net_value = 155 then 1 else 0 end as netvalue155,
                case when v.net_value = 135 then 1 else 0 end as netvalue135
            from locations l 
                left join bookings b on l.id = b.location_id 
                left join vouchers v on  b.voucher_code = v.voucher_code
        ) a
        right join locations l on l.id = a.id
    group by a.location_name

编辑

我正在尝试下面的代码，它抛出错误SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sub.id' in on clause

$subQuery = DB::table('locations')
        ->select(
            'locations.id',
            'locations.location_name',
            DB::raw('"'.$request->get('dates').'" as dates'),
            DB::raw('sum(bookings.id) as number'),
            DB::raw('round(sum(bookings.final_price/1.2), 2) as paidbycard'),
            DB::raw('case when bookings.voucher_value = 155 then round(sum(bookings.voucher_value/1.2), 2) else 0.00 end as voucher155'),
            DB::raw('case when bookings.voucher_value = 135 then round(sum(bookings.voucher_value/1.2), 2) else 0.00 end as voucher135'),
            DB::raw('case when bookings.transfer_fee = 10 then round(sum(bookings.transfer_fee/1.2), 2) else 0.00 end as transfer_fee'))
        ->leftJoin('bookings', 'locations.id', '=', 'bookings.location_id');
    $meatBookQuery = DB::table('orders')->select(DB::raw('sum(orders_items.price) as total'))
        ->join('orders_items', 'orders.id', '=', 'orders_items.order_id')
        ->where('orders_items.item_name', 'The Meat Book');
    $booking = DB::table(DB::raw("({$subQuery->toSql()}) as sub, ({$meatBookQuery->toSql()}) as meatBook"))
        ->mergeBindings($subQuery)
        ->mergeBindings($meatBookQuery)
        ->select('sub.location_name', 'sub.dates', 'sub.number', 'sub.paidbycard', 'sub.voucher155', 'sub.voucher135', 'sub.transfer_fee', DB::raw('round(sum(sub.voucher155 + sub.voucher135 + sub.transfer_fee + sub.paidbycard), 2) as total'), 'meatBook.total')
        ->leftJoin('locations', 'locations.id', '=', 'sub.id')
        ->leftJoin('bookings', 'bookings.location_id', '=', 'sub.id')
        ->groupBy('sub.location_name');

Answer 1

首先

我经常看到有人问如何在Laravels query Builder中重建一个复杂的SQL查询。但并不是所有在SQL或MySQL中可能的操作都在Laravels Query Builder中作为函数实现。这意味着如果不使用原始SQL，就不能在query Builder中重新构建每个 SQL查询。

这对你的SQL查询意味着什么?

像子查询(from (select ...)部分)和case when ...部分没有在Query Builder中实现。因此，至少您必须将原始表达式与DB::raw()函数一起使用。我不确定sum()是否已经可能了，但你肯定会在文档中找到它。

其他东西，如joins是作为一个函数实现的:

$users = DB::table('users')
                 ->join('contacts', 'users.id', '=', 'contacts.user_id')
                 ->join('orders', 'users.id', '=', 'orders.user_id')
                 ->select('users.id', 'contacts.phone', 'orders.price')
                 ->get();

参见Laravel Documentation: Queries - Joins

您甚至可以将Query Builder函数与原始表达式混合使用:

$users = DB::table('users')
                 ->select(DB::raw('count(*) as user_count, status'))
                 ->where('status', '<>', 1)
                 ->groupBy('status')
                 ->get();

参见Laravel Documentation: Queries - Raw Expression

子查询示例:

$subQuery = DB::table('locations')
                 ->leftJoin('bookings', 'locations.id', '=', 'bookings.location_id')
                 ->leftJoin('vouchers', 'bookings.voucher_code', '=', 'vouchers.voucher_code')
                 ->select('locations.id', 'locations.location_name', 'bookings.places');
$query = DB::table(DB::raw("({$subQuery->toSql()} as sub"))
                 ->mergeBindings($subQuery)
                 ->select(...)
                 ->rightJoin(...)
                 ->groupBy('sub.location_name')
                 ->get();

所以你可以在query Builder中重新构建查询的某些部分，并在需要的地方使用原始表达式。

在构建查询时调试查询，Laravels查询日志功能非常有用。