Amit Merchant

Amit Merchant

A blog on PHP, JavaScript, and more

The new whereNot method in Laravel 9.x

March 4, 2022 ·

Sometimes, the things you would think are quite obvious and should exist without a doubt don’t exist at all.

One such thing is the new whereNot method for query builder/Eloquent in Laravel 9.x. You would be thinking a method as simple as this should definitely exist in Laravel but it isn’t.

So, to give you a primer, a typical where query in Laravel would look like this.

$books = DB::table('books')
                    ->where('rating', '>', 2)
                    ->where('author', 'Ruskin Bond')
                    ->get();

This query would fetch all the books which have a rating greater than 2 and whose author is “Ruskin Bond”.

The old way

Now, sometimes you would need to fetch all the records of the condition which is exactly the opposite of this. In such a case, the query can be re-written like so.

$books = DB::table('books')
                    ->where('rating', '<=', 2)
                    ->where('author', '!=', 'Ruskin Bond')
                    ->get();

This query will fetch all the records which have a rating of 2 or less and whose author isn’t “Ruskin Bond”.

Now, this query works perfectly fine but the way it is written is a little counter-intuitive in my opinion.

That’s where the new whereNot and orWhereNot method comes into play.

The whereNot and orWhereNot methods

This PR by Marco van Oort now adds two new methods, whereNot and orWhereNot, in Laravel 9.x

What these methods do is simple. This allows you to build “where not” SQL claused in your queries so that you can easily build intuitive queries.

For instance, if we want to rewrite the previous example using the whereNot method, here is how we can do it.

$books = DB::table('books')
            ->whereNot(function ($query) {
                $query->where('rating', '>', 2)
                    ->where('author', 'Ruskin Bond')
            })
            ->get();

You can utilize the same on Eloquent models as well.

$books = Book::whereNot(function ($query) {
                $query->where('rating', '>', 2)
                    ->where('author', 'Ruskin Bond')
            })
            ->get();

In the same way, you can use the orWhereNot method as well when you want to chain an “or where not” clause.

Learn the fundamentals of PHP 8 (and 8.1), the latest version of PHP, and how to use it today with my new book PHP 8 in a Nutshell. It's a no-fluff and easy-to-read guide to the latest features and nitty-gritty details of PHP 8. So, if you're looking for a quick and easy way to PHP 8, this is the book for you.

👋 Hi there! I'm Amit. I write articles about all things web development. If you like what I write and want me to continue doing the same, I would like you buy me some coffees. I'd highly appreciate that. Cheers!

Comments?