laravel - 转换原始 SQL 以使用 Eloquent 查询生成器

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 的情况下在查询生成器中重建每个 SQL 查询。

这对您的 SQL 查询意味着什么？

查询生成器中未实现子查询(from (select ...) 部分)和 case when ... 部分。因此，至少您必须将原始表达式与 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 文档:Queries - Joins

您甚至可以将查询生成器函数与原始表达式混合使用:

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

请参阅 Laravel 文档: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();

因此，您可以在查询生成器中重建查询的某些部分，并在任何需要的地方使用原始表达式。

在构建查询时调试查询 Laravels query logging功能可能非常有帮助。