Introduction
Laravel’s eloquent ORM is a powerful and fluid database query builder that makes working with PHP’s database interactions a breeze. Periodic queries based on timeframes like the last day, week, month, or year are commonplace. This tutorial is designed to enhance your expertise with Laravel Query Builder by guiding you through various scenarios of temporal data retrieval in your Laravel applications.
Getting Started
Before we tap into different time-bound queries, make sure you have a Laravel project up and running, along with the necessary database migrations and models that you will be querying against. If you haven’t set up a Laravel project before, consider following one of the following articles:
- How to install Laravel on Windows and Mac
- How to set up Laravel with Docker Compose
- How to deploy Laravel to DigitalOcean with Laravel Forge
Basic Setup
Your model should extend the Illuminate\Database\Eloquent\Model class and, optionally, you can specify the table associated with the model. Here is a basic example of a model named Post:
use Illuminate\Database\Eloquent\Model;
class Post extends Model {
protected $table = 'posts';
}
With your model in place, you can now explore various ways to retrieve data based on a range from the current time.
Selecting Rows from the Last Day
To select rows from your database that were created in the last 24 hours, you can use the where method provided by the query builder. Here’s how:
$posts = Post::where('created_at', '>=', now()->subDay())->get();
This will retrieve all the posts that have a created_at timestamp from the last day. Replace Post with your actual model’s name. The output depends on the contents of your database.
Selecting Rows from the Last Week
To select records from the past week, switch subDay() with subWeek():
$posts = Post::where('created_at', '>=', now()->subWeek())->get();
The output will include all posts dated within the previous week up to the current moment.
Selecting Rows from the Last Month
Retrieving data from the last month requires a simple modification to use the subMonth() method:
$posts = Post::where('created_at', '>=', now()->subMonth())->get();
Your output will consist of posts created in the last 30 days.
Selecting Rows from the Last Year
Similarly, for data from the past year, make use of the subYear() function:
$posts = Post::where('created_at', '>=', now()->subYear())->get();
Now your output will contain posts from the past 365 days.
Advanced Date and Time Queries
Laravel’s Query Builder lets you build more complex queries to fine-tune your selections even further.
Between Two Dates
You can also use the whereBetween method to select rows between two dates:
$posts = Post::whereBetween('created_at', [now()->subMonth(), now()])->get();
This returns posts created in the last month but allows for more precise boundaries than the subMonth() method.
Using Carbon for More Advanced Date Operations
Carbon is a simple PHP API extension for DateTime and allows more complex operations. Laravel comes with Carbon by default. Here’s how to use it for retrieving posts from the last weekday:
use Carbon\Carbon;
$posts = Post::where('created_at', '>=', Carbon::today()->subWeekday())->get();
This retrieves all posts since the previous weekday.
Custom Interval Retrieval
If you want to retrieve records within any custom timeframe:
$daysAgo = 10;
$posts = Post::where('created_at', '>=', now()->subDays($daysAgo))->get();
This example gets all the posts from the last 10 days.
Conclusion
Laravel’s Query Builder provides an intuitive and flexible way to interact with your database. By incorporating time intervals into your queries, your application can efficiently extract time-sensitive data while maintaining clean and readable code. Remember, leveraging existing methods like subDay(), subWeek(), and so on simplifies your development process.
