Pro Tip: Filtering Queries with Laravel Eloquent's when() and whereRaw('0 = 1') for Accurate Results

Published in Database, Laravel, PHP on Nov 30, 2023

Laravel's Eloquent provides a handy feature called when() that allows you to conditionally add filters to your queries. However, there might be cases where none of the conditions are met, and you want to ensure that no results are returned. In such cases, you can use the whereRaw('0 = 1') method.

Take the following example, where we have a Post model:

Copied!

 1$posts = App\Models\Post::query()
 2    ->when($filter === 'only-new', function ($query) {
 3        return $query->where('is_new', true);
 4    })
 5    ->when($filter === 'only-featured', function ($query) {
 6        return $query->where('is_featured', true);
 7    })
 8    ->when($filter === null, function ($query) { 
 9        return $query->whereRaw('0 = 1'); 
10    }) 
11    ->get();

$posts = App\Models\Post::query()
    ->when($filter === 'only-new', function ($query) {
        return $query->where('is_new', true);
    })
    ->when($filter === 'only-featured', function ($query) {
        return $query->where('is_featured', true);
    })
    ->when($filter === null, function ($query) {
        return $query->whereRaw('0 = 1');
    })
    ->get();

In the last scenario, if the $filter variable is null or does not match any of the previous conditions, we use whereRaw('0 = 1'). This condition ensures that no results are returned because it is always false.

By including ->whereRaw('0 = 1'), the query won't execute Post::get() and it effectively prevents all the following conditions from being applied. This means that if the filter condition is not met, you won't risk returning all results in the database.

In summary, using ->whereRaw('0 = 1') in this context allows you to handle scenarios where none of the conditions are met, ensuring that no results are returned.