CODEWITHSUNDEEP
phpmysqllaravelbuilder
Wahsei
Wahsei

Reputation: 307

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

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

Upvotes: 1

Views: 1642

Answers (1)

sepehr
sepehr

Reputation: 18505

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

Upvotes: 2

Related Questions