Laravel: whereNull & whereNotNull Eloquent Query with Example

Laravel: whereNull & whereNotNull Eloquent Query with Example

Laravel Database query builder is a very powerful and convenient interface for building SQL queries directly in Laravel. It uses PDO binding behind the scenes you won't have to worry about SQL Injection attacks. Here, we will talk two of many methods provided by Laravel Database query builder and can be used both in Database query builder as well as Eloquent Query - whereNull() and whereNotNull() and same methods but in OR conditions - orWhereNull() and orWhereNotNull().

Laravel Database query builder is a very powerful and convenient interface for building SQL queries directly in Laravel. It uses PDO binding behind the scenes you won't have to worry about SQL Injection attacks.

Here, we will talk two of many methods provided by Laravel Database query builder and can be used both in Database query builder as well as Eloquent Query - whereNull() and whereNotNull() and same methods but in OR conditions - orWhereNull() and orWhereNotNull().

These methods are very simple actually and easy to use. Let's review the syntax:

Syntax:

->whereNull('columnname')

->whereNotNull('columnname')

->orWhereNull('columnname')

->orWhereNotNull('columnname')

whereNull() & orWhereNull() Query Example:

whereNull() method will put conditions that column must be empty

orWhereNull() method will too mean that column must be empty but it's with OR connector.

Let's review example for both whereNull() and orWhereNull() query builder methods:

//Let's find users with null (empty) names:

$users = User::whereNull('name')->get();
//select * from users where name is null;

//oh wait! I also want to check non-verified users
//hmm.. so basically I want to get all users whose name is empty OR who are not email verified yet
//Let's try orWhereNull()!

$users = User::whereNull('name')->orWhereNull('email_verified_at')->get();
//select * from users where name is null or email_verified_at is null;

//hurray!! Got my results

This is simple example to explain the usage of whereNull() and orWhereNull(). Ofcourse, you can and will use it in bit more complex eloquent queries.

whereNotNull() & orWhereNotNull() Query Example:

whereNotNull() method means column must be not null

orWhereNotNull() method is same as whereNotNull() except it uses OR connector.

Let's review example:

//Let's find users whose email is not null (we need emails, right?)

$users = User::whereNotNull('email')->get();
//(select * from users where email is not null;)

//Eh! Though Email is not that necessary for me! I can live with names if emails are not present!

//Okay, let's use orWhereNotNull() too then

$users = User::whereNotNull('email')->orWhereNotNull('name')->get();
//(select * from users where email is not null or name is not null;)

//Result? Users must have atleast one of two columns - name or Email. Both can be present, no issues there

So, this is how you can use null shorthand for Laravel Query where command in various ways to check against null values in database.

laravel

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Php how to delete multiple rows through checkbox using ajax in laravel

In this article i will let you know to delete multiple rows through checkbox using ajax in laravel and before delete we will give a confirmation message.

How to Hire Laravel Developers and How Much Does It Cost.

Want to make the most out of the latest Laravel technology? Here is your guide on how to hire the best Laravel developer, and at the right price. 

How to perform migration rollback in laravel

Sometimes we need to rollback one specific migration. Here, in this article we will discuss about the migration rollback in laravel.

Laravel Development Company

Skenix Infotech is a top Laravel Website Development Company with Expert Laravel Developers that provides robust Laravel Development Services at fair costs.

5 Laravel’s Hidden Gems

Spread the love1. Stop on first validation error By default, Laravel will check for all validation rules and return a list of errors. But if you want to stop this process after first validation failure, that’s how you can achieve…Read More→