Laravel sql query .. if condition in Where

Question

I just started to learn Laravel and converting the regular PHP code to Laravel. I need to execute this query in Laravel, but failed.

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

This is what I came up with, but it failes.

$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');

Corrected query based on the answer below

$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();

changed from get() to first as I wanted it to return as one row

Answer 1

You can dump the generated query by the query builder and compare it to the original query:

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());

It returns:

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

Which is almost identical to your original query. You need to chain the calls with a final get() to retrieve the results:

$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