Introduction
Working with databases means you’ll eventually need to query for records where a particular column is null or not null. In Laravel’s Eloquent ORM, this is a straightforward process, offering clear and expressive methods. This tutorial will explore how to find records with null or non-null columns using Eloquent with numerous code examples ranging from basic to complex.
Fundamentals of Null Checking in Eloquent
Eloquent offers a couple of methods dedicated to handling null values: whereNull and whereNotNull. Let’s begin with a simple example. Imagine you have a model User and you want to find all users that don’t have an email address assigned.
users = User::whereNull('email')->get();
This query will retrieve all users where the email column is null. To complement this, using whereNotNull will retrieve all users with a non-null email address:
$users = User::whereNotNull('email')->get();
Query Scopes for Reusability
Learning to minimize redundancy in your code is a cornerstone of good software development. Laravel’s query scopes allow for code reusability. Let’s define a scope in the User model for users with no email.
public function scopeWithoutEmail($query) {
return $query->whereNull('email');
}
With this scope, you can now call User::withoutEmail()->get() to achieve the same result as before, but with a much cleaner syntax.
Advanced Eloquent Queries
As you become more comfortable with these methods, you can start combining them with other Eloquent features. For instance, you can order the results:
$users = User::whereNull('email')
->orderBy('created_at')
->get();
Or even use it with whereHas and whereDoesntHave when dealing with relationship existence checks. For example, finding users with no registered blog posts could look like:
$users = User::whereDoesntHave('posts')->get();
Using Joins and Null Checks
Sometimes you need to perform more complex queries, involving joining tables together, and still check for null conditions. If, for example, you wanted to get all users who do not have a corresponding record in the orders table, you could use:
$users = User::leftJoin('orders', 'users.id', '=', 'orders.user_id')
->select('users.*')
->whereNull('orders.user_id')
->get();
Conclusion
In summary, Eloquent’s whereNull and whereNotNull methods provide concise, expressive ways to query for null and non-null column values in your database. By leveraging these methods, you can create readable and maintainable queries that fit within the expressive nature of Eloquent ORM.
