In this post, we'll explore two handy methods in Laravel's Eloquent ORM: whereNull and whereNotNull. These methods allow you to filter query results based on the presence or absence of null values. Let's dive in and see how they work with a few examples!
Example 1: Let's say you want to retrieve all users who haven't provided their email address. You can accomplish this using the whereNull method as follows:
$users = User::whereNull('email')->get();
In the above example, we use the whereNull method on the User model with the column name 'email'. This query will fetch all users whose email column is null or empty.
Example 2: Suppose you have a table named invoices, and you want to fetch all unpaid invoices where the payment date is not set. You can achieve this using the whereNull and whereNotNull methods together:
$unpaidInvoices = Invoice::whereNull('payment_date')
->whereNotNull('due_date')
->get();
In the above example, we use the whereNull method to filter invoices where the payment_date column is null. We also chain the whereNotNull method to further refine the query and retrieve only the invoices with a non-null due_date.
Example 3: Let's consider a scenario where you have a products table, and you want to fetch all products that have a null value for the discount_percentage column:
$productsWithoutDiscount = Product::whereNull('discount_percentage')->get();
In this example, we use the whereNull method to retrieve all products with a null value in the discount_percentage column.
The whereNull and whereNotNull methods provide a straightforward way to filter query results based on the presence or absence of null values. They are particularly useful when dealing with optional or nullable fields in your database.