WhereAll() and WhereAny() in laravel 11

In Laravel 11, the Query Builder introduces two powerful functions: whereAll() and whereAny(), anticipated to see widespread adoption. Sometimes, you might need to impose identical conditions on multiple columns in a database query. For instance, you may wish to fetch all entries where any or all column in a specified list matches a certain pattern. Utilizing the SQL LIKE operator, these functions become essential tools in query construction.

WhereAll()

The whereAll method in laravel allows you to fetch records where all specified columns meet a particular condition. For instance, if we need to fetch all the contents from the blogs table where title and description match the laravel keyword. We can use this as:

// Using whereAll method
$blogs = Blog::query()
            ->whereAll(['title','description'], 'LIKE', '%laravel%')
            ->get();

Furthermore, integrating user search input becomes effortless:

use Illuminate\Http\Request;

public function search(Request $request)
{
    $search = $request->get('q');
    $blogs = Blog::query()
            ->whereAll([
                'title',
                'description',
              ], 'LIKE', '%' . $search. '%')
            ->get();
}

In this way you can use the whereAll function in query.

WhereAny()

WhereAny serves as the counterpart to above implementation. While whereAll requires all specified columns to meet a condition for data retrieval, whereAny only necessitates one column to match the condition. Lets rewrite the above case for this case:

// Using whereAny method
$blogs = Blog::query()
            ->whereAny(['title','description'], 'LIKE', '%laravel%')
            ->get();

Implement user search input as:

use Illuminate\Http\Request;

public function search(Request $request)
{
    $search = $request->get('q');
    $blogs = Blog::query()
            ->whereAny([
                'title',
                'description',
              ], 'LIKE', '%' . $search. '%')
            ->get();
}

In this way you can implement the whereAny function on query.

This is not only changing the function from All to Any, but this will impact on SQL Query. You can see use the difference between these by generating the sql from the above as:

// To dubug and see the SQL of below query
$blogs = Blog::query()
            ->whereAny(['title','description'], 'LIKE', '%laravel%')
            ->ddRawSql();

For more descriptive implementation you can always visit the official documentation.

All code

// Using whereAll method
$blogs = Blog::query()
            ->whereAll(['title', 'description'], 'LIKE', '%laravel%')
            ->get();

// Using whereAny method
$blogs = Blog::query()
            ->whereAny(['title', 'description'], 'LIKE', '%laravel%')
            ->get();

// Implementing with request search data
use Illuminate\Http\Request;

public function search(Request $request)
{
    $search = $request->get('q');

    // Using whereAll method
    $blogs = Blog::query()
            ->whereAll(['title', 'description'], 'LIKE', '%' . $search. '%')
            ->get();

    // Using whereAny method
    $blogs = Blog::query()
            ->whereAny(['title', 'description'], 'LIKE', '%' . $search. '%')
            ->get();
}

Happy Coding !!