Laravel Query Builder where is NULL no result

Question

I have code as below to get shipment data where pdf_url is not NULL;

$shipment_data = DB::table('shipment')->where([
 'shipment_date' => '2017-12-11', ['pdf_url', '<>', 'NULL']])->get();

This has no problem, I get the data I need, but when I'm trying to use the same code to get the data with pdf_url is NULL, it has no result.

$shipment_data = DB::table('shipment')->where([
 'shipment_date' => '2017-12-11', ['pdf_url', '=', 'NULL']])->get();

What do I missing? I am very sure the DB record is there. I also tried other formats but still no result;

$shipment_data = DB::table('shipment')->where([
 'shipment_date' => '2017-12-11', ['pdf_url', 'NULL']])->get();

And

$shipment_data = DB::table('shipment')->where([
 'shipment_date' => '2017-12-11', 'pdf_url' => 'NULL'])->get();

EDIT: I can use whereRaw, but I'll prefer to use where instead. Code below has no issue;

$shipment_data = DB::table('shipment')
 ->whereRaw('shipment_date = "2017-12-11" AND pdf_url is NULL')->get();

Thanks @Ben thats probably the reason. I guess I have no choice to use `whereNull` or `whereRaw` instead — sulaiman sudirman, Dec 12 '17 at 02:12

score 14 · Answer 1 · answered Dec 12 '17 at 02:05

14

Use whereNull

$shipment_data = DB::table('shipment')
            ->whereNull('pdf_url')->get();

answered Dec 12 '17 at 02:05

sumit

I'm aware of `whereNull` as well as `whereRaw`, I'm just wondering why it doesnt work when I'm using `where` with not NULL is okay – sulaiman sudirman Dec 12 '17 at 02:09
1

You can't compare `NULL` using `=`, `<>`, etc. but rather you must use `IS NULL` and `IS NOT NULL`. – Tim Biegeleisen Dec 12 '17 at 02:11
@TimBiegeleisen pointed exactly the same thing – sumit Dec 12 '17 at 02:12

score 3 · Answer 2 · edited May 12 '20 at 09:55

3

try this:

$records = DB::table('shipment')
  ->where('shipment_date','2017-12-11')
  ->whereNull('pdf_url')
  ->get();

edited May 12 '20 at 09:55

Peter Krebs

answered Dec 12 '17 at 07:32

Ajay

score 1 · Answer 3 · answered Dec 12 '17 at 02:12

1

You can use whereNull

The whereNull method verifies that the value of the given column is NULL.

answered Dec 12 '17 at 02:12

3 Answers3