Eloquent: Find records in last day/week/month/year

Introduction

In the world of web development, working with dates and times is an everyday necessity. If you’re using Laravel, Eloquent makes this task especially streamlined. In this tutorial, we’ll be diving into how to find records based on time criteria such as the last day, week, month, and year with Eloquent ORM.

Understanding Date and Time in Eloquent

Before we begin writing actual queries, let’s refresh our understanding of how Eloquent handles dates and times. By default, Eloquent will convert created_at and updated_at columns to instances of Carbon, a PHP library that extends the native DateTime class. This conversion enables us to use a myriad of helpful methods when dealing with these fields.

Let’s set the stage by assuming you have an Eloquent Model named Post. Make sure your model has timestamp fields enabled:

class Post extends Model
{
    protected $dates = ['created_at', 'updated_at'];
}

Finding Records from the Last Day

To select records where created_at is within the last 24 hours, you can use the whereDate helper method combined with Carbon’s now method:

$posts = Post::where('created_at', '>=', \\Carbon::now()->subDay())->get();

This query retrieves posts created in the last day. If you also want to exclude the current day’s data to get a full 24-hour range from the previous day, you may adjust the query:

$posts = Post::whereDate('created_at', '=', \\Carbon::yesterday())->get();

Finding Records from the Last Week

Moving further back in time, if you need to retrieve records from the last 7 days, Laravel provides a straightforward way to accomplish this:

$posts = Post::where('created_at', '>=', \\Carbon::now()->subWeek())->get();

If your requirement is to get records from the previous calendar week, you’ll need to fine-tune the query a bit:

$startOfWeek = \\Carbon::now()->startOfWeek()->subWeek();
$endOfWeek = \\Carbon::now()->startOfWeek()->subDay();
$posts = Post::whereBetween('created_at', [$startOfWeek, $endOfWeek])->get();

Finding Records from the Last Month

To get records from the last month, Eloquent allows us to subtract a month from the current date:

$posts = Post::where('created_at', '>=', \\Carbon::now()->subMonth())->get();

For retrieving records specifically from the previous calendar month:

$startOfMonth = \\Carbon::now()->startOfMonth()->subMonth();
$endOfMonth = \\Carbon::now()->startOfMonth()->subDay();
$posts = Post::whereBetween('created_at', [$startOfMonth, $endOfMonth])->get();

Finding Records from the Last Year

To select records from the last year, we adjust our query to subtract one year from the current time. The following query fetches all the posts from the past year:

$posts = Post::where('created_at', '>=', \\Carbon::now()->subYear())->get();

For records from the previous calendar year, the query will be:

$startOfYear = \\Carbon::now()->startOfYear()->subYear();
$endOfYear = \\Carbon::now()->startOfYear()->subDay();
$posts = Post::whereBetween('created_at', [$startOfYear, $endOfYear])->get();

Advanced Time-Based Eloquent Queries

Now, we’ll take our queries to the next level by adding additional constraints and leveraging scopes.

Using Scopes for Reusable Queries

To avoid repeating the same logic in multiple queries throughout your application, you can define a query scope in your Eloquent model:

class Post extends Model
{
    // Previous definition of the Post model

    public function scopePastMonth($query)
    {
        return $query->where('created_at', '>=', \\Carbon::now()->subMonth());
    }
}

Using the scope is simple:

$posts = Post::pastMonth()->get();

Combining Time-Based Queries with Other Clauses

You can combine time-based queries with other Eloquent clauses for more refined results:

$posts = Post::pastMonth()
    ->where('status', 'published')
    ->orderBy('views', 'DESC')
    ->limit(10)
    ->get();

Conclusion

By leveraging the powerful combination of Eloquent and Carbon, finding records based on timeframes such as the last day, week, month, or year becomes a breeze in Laravel. Whether building complex or simple data retrieval requests, Eloquent’s expressive syntax coupled with Carbon’s date manipulation methods offers an efficient and developer-friendly way to create dynamic and date-sensitive applications.