Laravel 10.47: new whereAny() & whereAll()

This week in Laravel, two new eloquent methods were merged into the framework. These methods, originally contributed by Alexander as a ->whereMultiple() method, were eventually finessed into two new methods. Lets go over some basic usages of these methods and see how they work.

You can see the original pull request here: https://github.com/laravel/framework/pull/50344

whereAny() method

This method will stop developers having to chain the ->orWhere()method on their queries and instead, encapsulate all of that logic inside a single ->whereAny() method. Therefore, instead of the following query being written

$search = '%Otwell%';

User::query()
      ->where(function ($query) use ($search) {
          $query
              ->where('first_name', 'LIKE', $search)
              ->orWhere('last_name', 'LIKE', $search)
              ->orWhere('email', 'LIKE', $search)
              ->orWhere('phone', 'LIKE', $search);
      })

It can instead now be refactored into ->whereAny()which will search an array of columns provided for the search query provided. It will look something like the below code snippet

$search = 'Otwell';

User::whereAny(['first_name','last_name','email'], 'LIKE', "%$search%")

The SQL that this new method will output, using logic from above is below. This will return any result if one or more columns contain the search query.

SELECT * FROM "users" WHERE (
  "first_name" LIKE "%Otwell%" 
  OR "last_name" LIKE "%Otwell%" 
  OR "email" LIKE "%Otwell%"
)

whereAll() method

Similar to the whereAny() a whereAll() method has been contributed, which will check that the search query exists in all columns instead of just one or more. The function looks like the following;

$search = 'Otwell';

User::whereAll(['first_name','last_name','email'], 'LIKE', "%$search%")

The SQL that ->whereAll()will be very similar to the ->whereAny() which replaces the OR from the SQL to AND, which will only return results if ALL fields contain the search query.

SELECT * FROM "users" WHERE (
  "first_name" LIKE "%Otwell%" 
  AND "last_name" LIKE "%Otwell%" 
  AND "email" LIKE "%Otwell%"
)

Closing Words

Even one week out from Laravel 11, It’s always encouraging seeing updates coming out on a regularly scheduled basis from the Laravel Team.