Eloquent: Find Records Between Two Dates

Introduction

When working with applications that have time-sensitive data, it is often necessary to retrieve records that fall between two specific dates. In Laravel’s Eloquent, this is a common task for developers. Eloquent provides an elegant and fluent interface to query the database with an expressive syntax. In this tutorial, we will discuss multiple ways to achieve date range queries using Eloquent.

Prerequisites

• Basic knowledge of Laravel and MVC architecture
• Working Laravel environment
• A database with at least one table containing date or datetime columns

Using whereBetween Method

The simplest way to retrieve records between two dates in Eloquent is by using the whereBetween method. Here’s a basic example:

$records = ModelName::whereBetween('date_column', ['2023-01-01', '2023-01-31'])->get();

The above code will retrieve records from the ModelName where the date_column is between ‘2023-01-01’ and ‘2023-01-31’. Below is a detailed guide with multiple examples to help you master date range queries in Eloquent.

Basic Date Range Query

Let’s start with a basic example:

use App\Models\Order;

// Fetch orders between two dates
$startDate = '2021-07-01';
$endDate = '2021-07-15';
$orders = Order::whereBetween('created_at', [$startDate, $endDate])->get();

// Output
foreach ($orders as $order) {
    echo $order->id . ' - ' . $order->created_at . "\n";
}

This will output the IDs and ‘created_at’ dates of the orders that were placed between July 1, 2021, and July 15, 2021.

Advanced Usage with Time

Sometimes, you may want to filter records by datetime, not just by date. In this case, you will need to include the time in your filtering:

$orders = Order::whereBetween('created_at', [
    '2021-07-01 00:00:00',
    '2021-07-15 23:59:59'
])->get();

This example includes the full datetime range for the provided dates. It will select all orders that were placed in the first 15 days of July 2021, up until the last second of the last day.

Using Carbon for Date Manipulation

Working with raw date strings is fine, but sometimes it is more convenient to work with a date manipulation library like Carbon, which is built into Laravel. Below is an example of how you can use Carbon:

use Carbon\Carbon;

$startDate = Carbon::createFromFormat('Y-m-d H:i:s', '2021-07-01 00:00:00');
$endDate = Carbon::createFromFormat('Y-m-d H:i:s', '2021-07-15 23:59:59');

$orders = Order::whereBetween('created_at', [$startDate, $endDate])->get();

Carbon allows you to handle dates in a much more approachable and flexible way, including the manipulation of dates, differences between dates, and much more.

Scope for Date Range Query

To make our code cleaner, we could define a local scope within our Eloquent model:

use Illuminate\Database\Eloquent\Builder;

// Inside your Eloquent Model

public function scopeCreatedBetween(Builder $query, $startDate, $endDate) {
    return $query->whereBetween('created_at', [$startDate, $endDate]);
}

// Usage of the scope
$orders = Order::createdBetween($startDate, $endDate)->get();

Scopes can be incredibly useful to keep your controller actions more readable and maintain a separation of concerns within your model layer.

Dynamically Generating Date Ranges

To dynamically generate a range of dates for the previous week, month, year, etc., you can do it like this:

$startDate = Carbon::now()->startOfWeek();
$endDate = Carbon::now()->endOfWeek();

$orders = Order::createdBetween($startDate, $endDate)->get();

This will give you orders made from the start to the end of the current week.

Conclusion

Whether you’re running basic or complex queries, Eloquent’s fluency in handling a date range is an invaluable asset. We’ve explored the primary ways to capture data within a specified timeline, reinforcing the significance of Laravel’s eloquent ORM for efficient database interaction.