How To Effortlessly Retrieve Data with Date Scopes in Laravel
Last Updated on April 18, 2023
Laravel provides a powerful ORM for querying the database. At times, we might want to query the database in a very specific way. This might include using date scopes(ranges) to fetch this data.
For example, if you are building an application where data reporting is the bread and butter of the application, you might need to introduce a way of querying this data in specific date ranges. You might want to check data for “today”, “yesterday” “last 7 days”, or even “quarter to date”.
This brings a lot of complexities when you try to fetch these records using raw SQL. Luckily there is a way we can leverage Eloquent ORM to fetch our records using specific date ranges.
In this guide, I will show you how to prepare simple Date Scopes for your use case and how to use them to query your Database.
What are Date Scopes?
Date Scopes allow you to filter database records based on time ranges. These are methods you can define in your model or anywhere in your codebase that allow you to filter records based on a specific date or a range of dates.
For example, you could define a date scope called “today” that retrieves all records created on the current day.
Date Scopes are useful when you are working on time-sensitive data or when working on a data reporting tool such as an analytics tool, or even an e-commerce platform.
In this guide, we will assume we are building a simple URL shortener service. We want to display various data such as clicks, visitors, etc based on the date range a user wants to filter with.
I will not create the simple URL shortener from scratch because I had already created it in this article.
Setting up the Database and Model.
In our application, we might want to keep track of Visits generated by the links we had shortened earlier. Therefore, we will create a new model called Visits
php artisan make:model Visits -mWe will then add this logic to both the Model and migration file
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Visits extends Model
{
use HasFactory;
protected $fillable = [
'ip', 'metadata', 'link_id', 'country'
];
protected $casts = [
'metadata' => 'array',
];
public function links()
{
return $this->belongsTo(Links::class, 'link_id');
}
}
Visit’s Model
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('visits', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('link_id')->index();
$table->string('ip');
$table->string('country')->nullable();
$table->json('metadata');
$table->timestamps();
$table->foreign('link_id')->references('id')->on('links')->cascadeOnDelete();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('visits');
}
};
Creating Date Scopes
Once our model is ready, we will create the Date Scopes. I will use a dedicated Class that will contain the logic.
//App/Utils/DateFilter.php
<?php
namespace App\Utils;
use Exception;
use Illuminate\Support\Carbon;
class DateFilter
{
public static function getDatesForPeriod($period, $customStart = null, $customEnd = null)
{
switch ($period) {
case 'today':
$start = now()->startOfDay();
$end = now()->endOfDay();
break;
case 'yesterday':
$start = now()->subDay()->startOfDay();
$end = now()->subDay()->endOfDay();
break;
case 'last 24 hours':
$start = now()->subHours(24);
$end = now()->endOfDay();
break;
case 'last 7 days':
$start = now()->subDays(6)->startOfDay();
$end = now()->endOfDay();
break;
case 'this week':
$start = now()->startOfWeek();
$end = now()->endOfDay();
break;
case 'last 30 days':
$start = now()->subDays(29)->startOfDay();
$end = now()->endOfDay();
break;
case 'this month':
$start = now()->startOfMonth();
$end = now()->endOfDay();
break;
case 'last 90 days':
$start = now()->subDays(89)->startOfDay();
$end = now()->endOfDay();
break;
case 'last month':
$start = now()->subMonth()->startOfMonth();
$end = now()->subMonth()->endOfMonth();
break;
case 'quarter to date':
$start = now()->startOfQuarter();
$end = now()->endOfDay();
break;
case 'this year':
$start = now()->startOfYear();
$end = now()->endOfDay();
break;
case 'last year':
$start = now()->subYear()->startOfYear();
$end = now()->subYear()->endOfYear();
break;
case 'all time':
$start = Carbon::createFromDate(1900, 1, 1)->startOfDay();
$end = Carbon::today()->endOfDay();
break;
case 'custom':
if ($customStart && $customEnd) {
$start = Carbon::parse($customStart)->startOfDay();
$end = Carbon::parse($customEnd)->endOfDay();
} else {
throw new Exception('Custom range requires start and end dates.');
}
break;
default:
throw new Exception('Invalid period specified.');
}
return ['start' => $start, 'end' => $end];
}
Here we have defined a simple switch case that determines what date range we are requesting. Carbon Dates is pretty useful here because it contains various helper methods that help us prepare these date ranges.
Finally, we are returning an array of dates with the start and end keys which we will use to query the database.
Using Date Scopes in Queries.
Now that we have our helper ready, we can now use it to query the Database.
We want to return the database records based on the period specified by the user. Therefore, we will create another function that will help us achieve that.
<?php
use App\Models\Visits;
...
public static function results($period, $customStart = null, $customEnd = null)
{
$dates = self::getDatesForPeriod($period, $customStart, $customEnd);
return Visits::with('links')
->whereBetween('created_at', [$dates['start'], $dates['end']])
->get();
}From the code above, we can see that we are using the whereBetween() function to fetch results that have a created_at value that is between the start and end date.
And that’s pretty much it. You can now add a simple date picker on your front end and enjoy fetching data from your database with ease.
Conclusion
In this tutorial, we have covered how to prepare a simple date scope and how to query your database records using these date scopes.
This is one way of solving this problem. Another option might be using this package to handle your date scopes and Transactions.
I hope this guide was insightful. Thank you for reading.
