Laravel 8 Eloquent whereBetween query example

  7223 views   11 months ago Laravel

Laravel provides convenient and many query builder methods to fetch data from database queries. One of the method is whereBetween. Sometimes you may want to fetch records for a field value between two values. You can even get records between two dates. For example, getting records between id 1 to 100 or created_at between two dates. Then whereBetween method is for that.

In this article, we will see how we can get records in which specific field value between two value. whereBetween() method compares field value with two values. Let's first get records by id between two values. For example, we can find records between ids 1 and 100 example

/**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $users = User::whereBetween('id', [1, 100])
            ->get();

    dd($users);
}

This will return all users having ids between 1 and 100. Now let's get users registered from last 30 days. Laravel by default installed Carbon. We can use Carbon to count 30 days.

/**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $start_date = \Carbon\Carbon::now()->subDays(30);
    $end_date = \Carbon\Carbon::now();

    $users = User::whereBetween('created_at', [$start_date, $end_date])
            ->get();

    dd($users);
}

We can even use standard timestamp value instead of Carbon. For example:

/**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $start_date = '2021-09-08 00:00:00';
    $end_date = '2021-10-07 23:59:59';

    $users = User::whereBetween('created_at', [$start_date, $end_date])
            ->get();

    dd($users);
}

We can also use whereBetween() method with \DB facade instead of Model. For example,

/**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $users = \DB::table('users')
            ->whereBetween('id', [1, 100])
            ->get();

    dd($users);
}

I hope it will help you.

Author : Harsukh Makwana
Harsukh Makwana

Hi, My name is Harsukh Makwana. i have been work with many programming language like php, python, javascript, node, react, anguler, etc.. since last 5 year. if you have any issue or want me hire then contact me on [email protected]