php - Laravel sql 查询 .. if 条件在 Where

Question

我刚开始学习 Laravel 并将常规的 PHP 代码转换为 Laravel。我需要在 Laravel 中执行此查询，但失败了。

SELECT sum(qty_del) as delivery from delivery_sap a where YEAR(a.bill_date) + IF(MONTH(a.bill_date)>6, 1, 0) = 2017

这是我想出来的，但是失败了。

$data = DB::table('delivery_sap')
          ->select(DB::raw('sum(qty_del) as delivery'))
          ->whereRaw('YEAR(a.bill_date) + IF(MONTH(a.bill_date)>6, 1, 0) = 2017');

根据下面的答案更正了查询

$data = DB::table('delivery_sap')
      ->select(DB::raw('sum(qty_del) as delivery'))
      ->whereRaw('YEAR(bill_date) + IF(MONTH(bill_date)>6, 1, 0) = 2017')
      ->first();

从 get() 更改为 first，因为我希望它作为一行返回

Answer 1

您可以转储查询构建器生成的查询并将其与原始查询进行比较:

use Illuminate\Support\Facades\DB;

$query = DB::table('delivery_sap')
    ->select(DB::raw('sum(qty_del) as delivery'))
    ->whereRaw('YEAR(a.bill_date) + IF(MONTH(a.bill_date)>6, 1, 0) = 2017');

dd($query->toSql());

它返回:

"select sum(qty_del) as delivery from `delivery_sap` where YEAR(a.bill_date) + IF(MONTH(a.bill_date)>6, 1, 0) = 2017"

这与您的原始查询几乎相同。您需要使用最终的 get() 链接调用检索结果:

$data = DB::table('delivery_sap AS a')
    ->select(DB::raw('sum(qty_del) as delivery'))
    ->whereRaw('YEAR(a.bill_date) + IF(MONTH(a.bill_date)>6, 1, 0) = 2017')
    ->get(); // <= Here