Wahsei Wahsei - 3 days ago 5
MySQL Question

Lavarel sql query .. if condition in Where

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

Answer

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')
    ->select(DB::raw('sum(qty_del) as delivery'))
    ->whereRaw('YEAR(a.bill_date) + IF(MONTH(a.bill_date)>6, 1, 0) = 2017')
    ->get(); // <= Here
Comments