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

Use get() at the end of your whereRaw() and see what's the result... — Saumya Rastogi
– Saumya Rastogi, Commented Nov 29, 2016 at 14:01

sepehr · Accepted Answer · 2016-11-29 15:22:56Z

2

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

edited Nov 29, 2016 at 15:22

answered Nov 29, 2016 at 14:06

sepehr

18.7k7 gold badges87 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Wahsei Over a year ago

I got this log --> local.ERROR: exception 'Symfony\Component\Debug\Exception\FatalErrorException' with message 'Class 'App\Http\Controllers\DB' not found'

sepehr Over a year ago

First, you need to import the facade or the alias into the namespace using a use statement. See the updated answer.

Wahsei Over a year ago

GOT IT..Thanks ! .. Point to note -- I changed the a.bill_date to bill_date as well to make it work

sepehr Over a year ago

Yeah, right. Or you could just do this: DB::table('delivery_sap AS a').

Wahsei Over a year ago

I edited the question above with the right query. I used first() instead of get() to return one row

Collectives™ on Stack Overflow

Laravel sql query .. if condition in Where

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related