Laravel SQL is not performing as expected. No syntax error

Question

Can any Laravel developers help me understand why the following SQL statement is not working. I don't get any errors, it simply doesn't do what it should:

 DB::table('devices')
->leftJoin('hotel_device','hotel_device.device_id','devices.id')
->where("hotel_device.device_id","IS","NULL")
->delete();

This should remove all device ids where the leftJoin link returns as NULL (ie. where this device id is not being used in the "hotel_device" table).

If I run it as raw SQL directly in the database it works correctly. Note the "IS" condition rather than "=" condition, as I am refering to a NULL value. (using "=" didn't find any matching rows)

thanks.

I am positive eloquent uses ->whereNull('col') instead of ->where('col', 'IS', 'NULL'), could that be the issue? — Kiafar
– Kiafar, Commented Jan 9, 2020 at 15:03

scragar · Accepted Answer · 2020-01-09 15:00:34Z

1

You should use the whereNull method to compare to null rather than trying to filter it using the regular where method.

DB::table('devices')
->leftJoin('hotel_device','hotel_device.device_id','devices.id')
->whereNull("hotel_device.device_id")
->delete();

answered Jan 9, 2020 at 15:00

scragar

6,84530 silver badges37 bronze badges

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

1 Comment

Delmontee Over a year ago

Awesome, many thanks. I hadn't realised whereNull was a thing! Works perfectly.

Collectives™ on Stack Overflow

Laravel SQL is not performing as expected. No syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related