Database Queries with Laravel's whereNull

in this article, We’ll explore Laravel’s whereNull method with examples. The whereNull method helps to filter records based on null values. it creates a simple query Instead of writing complex SQL queries.

Laravel provides a clean and readable syntax that allows developers to null-related queries into the table. You can achieve this using the whereNull eloquent method.

What’s Wherenull

Developers can filter records in Laravel’s Eloquent ORM (Object-Relational Mapping) database table according to whether a given column is null or not by using the whereNull method.

Also checkout other Laravel 10 tutorials,

Laravel whereNull With Examples

This method allows developers to query databases based on the absence of a value in a specific column.

The Syntax:

$nullStatusRecords = DB::table('your_table')
->whereNull('col_name')
->get();

Let’s take a simple example to filter records using whereNull.

$nullRecords = Model::whereNull('column_name')->get();

in the above snippet, You need to replace ‘column_name’ with the actual column name. This fetches all records where the target column value is null.

Get all Employees that have Email ID

Let’s retrieve all employees who haven’t provided their email addresses:

$employeeWithoutEmail = Employee::whereNull('email')->get();

Using orWhereNull

You can also combine multiple conditions in a single query. The orWhereNull method can be used to include records where either of the specified conditions is met. Let’s take a simple example:

$resp = Model::where('status', '=', 'active')
->orWhereNull('status')
->get();

The query will return the result that have a null status as well as with a status of “active”.

Laravel whereNull With Models Relationships

You can also use whereNull with model relationships. Laravel’s Eloquent allows developers to define relationships between models. Like, I have an Employee model with a one-to-many relationship with a Dept model.

Let’s fetch those employees who haven’t associated with any department, You can use whereDoesntHave in combination with whereNull:

$empsWithoutDept = Employee::whereDoesntHave('dept', function ($query) {
$query->whereNull('emp_id');
})->get();

The above query will filter employees who don’t have any associated any department, and the nested whereNull query refines the result to include only those without a specified emp ID in the dept table.

Nullable Columns

You can also filter employees based on column nullable value. The whereNull method allows developer to filter out the data with nullable columns in database tables.

$res = Dept::whereNull('emp_id')->get();

Soft Delete Records Using whereNull()

The whereNull method is also useful to get records that are “soft deleted.” into the table.

$activeEmp = Employee::whereNull('deleted_at')->get();

Conclusion

We have discussed different ways to check the nullable values of a column. The whereNull method helps to find nullable columns, soft deletes, and Models relations, whereNull allows developers to create effective database queries.

Database Queries with Laravel’s whereNull