Laravel: The new `whereNone` Query

I love new features that enhance our ability to write clean and expressive code. I just made a small but significant addition to Laravel's query builder: the whereNone method.

The Logical Triad: Any, All, and None

Remember when Laravel introduced whereAny and whereAll methods? These powerful additions allowed us to write more elegant queries across multiple columns. Now, with the introduction of whereNone, we've completed what I like to call the "logical triad" of query methods.

Introducing whereNone

The whereNone method is the often overlooked "little sibling" in this family of methods. It operates on multiple columns, much like its counterparts, but uses negation to achieve the opposite effect of whereAny.

Let's break it down with an example:

php

$users = DB::table('users')
    ->where('active', true)
    ->whereNone([ 
        'first_name', 
        'last_name', 
        'email', 
    ], 'LIKE', 'einar%') 
    ->get();

This query will fetch all active users where either the first name, last name, or email doesn't start with 'einar'.

Under the Hood

When you run this query, Laravel generates the following SQL:

sql

SELECT *
FROM users
WHERE active = true AND NOT (
    first_name LIKE 'einar%' OR
    last_name LIKE 'einar%' OR
    email LIKE 'einar%'
)

Notice how it neatly wraps the conditions in a NOT clause, effectively finding records where none of the specified columns match the given condition.

Why whereNone Matters

Expressiveness: whereNone allows you to clearly state your intention in the code. It's immediately obvious that you're looking for records where none of the conditions are met.
DRY Code: Instead of chaining multiple where clauses with negations, you can group related conditions together.
Flexibility: It works seamlessly with other query builder methods, allowing for complex, yet readable queries.

Real-World Use Case

Imagine you're building a search feature for a music database. You want to find albums that don't contain explicit content across various fields:

php

$posts = DB::table('albums')
    ->where('published', true)
    ->whereNone([ 
        'title', 
        'lyrics', 
        'tags', 
    ], 'like', '%explicit%') 
    ->get();

This query efficiently retrieves all published albums where neither the title, lyrics, nor tags contain the word 'explicit'. This could be useful for filtering out potentially inappropriate content or for creating a family-friendly playlist.

Completing the Set

By adding whereNone, Laravel has completed the set of methods for handling constraints across multiple columns. We now have:

whereAll: All conditions must be true
whereAny: At least one condition must be true
whereNone: At least one condition cannot be true

This triad gives us a powerful and expressive way to handle complex queries involving multiple columns.

Wrapping Up

While whereNone might seem like a small addition, it's these thoughtful improvements that make Laravel such a joy to work with.

Give whereNone a try in your next project, and let me know how it simplifies your queries!

Want to learn more? Here are some resources for you:

Happy coding!