Generic charts implementation in Laravel
Learn how to develop aggregated charts grouped by any date attribute - an out-of-the-box solution for all Eloquent models.
Summary: A complete guide for simple generic charts implementation in Laravel. Learn how to develop an API endpoint that generates aggregated chart data grouped by different time divisions for any Eloquent model.?
Charts can be a good way of showing a certain type of data. There is a way to easily implement? a Laravel API endpoint that generates aggregated data grouped by different time divisions for any Eloquent model. For example, you can group user model data based on creation date, resulting in a chart containing the number of registrations divided over time. This kind of chart would be useful to read out any peaks in the number of people registered per chosen time division - providing insight into any registration campaigns or other actions that might have sparked that peak.?
Now, let’s set up our charts generator so you can better understand what exactly it can do. The first thing to do is add some models. For the sake of the example, we will have Post and User models (and their respective migrations):
app/Models/Post.php
class Post implements Chartable
{
use HasCharts;
protected $fillable = [
'user_id',
'content',
];
}
app/Models/User.php
class User implements Chartable
{
use HasCharts;
protected $fillable = [
'email',
'username',
];
}
database/migrations/users.php
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('email')->unique();
$table->string('username')->nullable()->unique();
$table->timestamps();
});
database/migrations/posts.php
Schema::create('posts', function (Blueprint $table) {
$table->id();
$table->text('content')->nullable();
$table->foreignId('user_id')->unsigned()
->references('id')->on('users');
$table->timestamps();
});
You may have noticed that both the Post and User class implement Chartable interface and use HasCharts trait. After we implement our charts generator, their usage is the only thing needed to make models “chartable”.? After everything is complete, fronted will gain access to endpoints to generate similar to the following:
Fig. 1: Monthly user registrations chart
Fig. 2: Chart showing the number of posts per day
Let's finally dive into the implementation. Here is the complete code of the Chartable interface and HasCharts trait:?
app/Traits/HasCharts.php
<?php
namespace App\Traits;
use Illuminate\Support\Facades\DB;
trait HasCharts
{
? ? public static function getDailyAggregated(
???????????string $aggregateColumn = 'created_at'
????): array
? ? {
? ? ? ? $aggregationQuery =?????
???????????'CONCAT(
????????????DAY('.$aggregateColumn.'),"-",
????????????MONTH('.$aggregateColumn.'),"-",
????????????YEAR('.$aggregateColumn.')
????????????) as date';
? ? ? ? return self::getAggregated($aggregationQuery);
? ? }
? ? public static function getMonthlyAggregated(
???????????string $aggregateColumn = 'created_at'
????): array
? ? {
? ? ? ? $aggregationQuery =????????
???????????'CONCAT(
???????????MONTH('.$aggregateColumn.'),"-",
???????????YEAR('.$aggregateColumn.')
???????????) as date';
? ? ? ? return self::getAggregated($aggregationQuery);
? ? }
? ? public static function getYearlyAggregated(
???????????string $aggregateColumn = 'created_at'
????): array
? ? {
? ? ? ? $aggregationQuery = 'YEAR('.$aggregateColumn.') as date';
? ? ? ? return self::getAggregated($aggregationQuery);
? ? }
? ? private static function getAggregated(string $aggregationQuery): array
? ? {
? ? ? ? $table = with(new static())->getTable();
? ? ? ? $result = DB::table($table)
? ? ? ? ? ? ->select([
? ? ? ? ? ? ? ? DB::raw($aggregationQuery),
? ? ? ? ? ? ? ? DB::raw('count(id) as total'),
? ? ? ? ? ? ])
? ? ? ? ? ? ->groupBy('date')
? ? ? ? ? ? ->get()
? ? ? ? ? ? ->toArray();
? ? ? ? return $result;
? ? }
}app/Models/Core/Chartable.php
<?php
namespace App\Models\Core;
interface Chartable
{
public static function getDailyAggregated(string $aggregateColumn = 'created_at'): array;
public static function getMonthlyAggregated(string $aggregateColumn = 'created_at'): array;
public static function getYearlyAggregated(string $aggregateColumn = 'created_at'): array;
}
Chartable interface simply defines methods implemented in the HasCharts trait. Each of these defined methods (getDailyAggregated, getMonthlyAggregated, getYearlyAggregated) is used to get query results aggregated by the requested column, the default column being created_at. You can notice that each method defines an $aggregateColumn variable. Said variable contains a query string that groups the result based on the requested time division. $aggregateColumn is the only real difference between these methods and in the actual query we are going to run on the database.
The query result will be grouped by the requested part of the date variable and selected in the following format for each method:
getDailyAggregated: dd-mm-yyy
getMonthlyAggregated: mm-yyy
getYearlyAggregated: yyyyy
In the next step, the getAggregated method gets called. This method runs the query on the database. To generalize this query, we need to run the query as raw SQL, meaning we need to know the name of the table we are running our query on. There are a few things to keep in mind here:
I didn’t want to use non-static methods here because I wanted to be able to use these methods without initializing the model class in question. Using self:: static reference is not possible here due to loading priorities, and it would return a reference to the trait class in this case.? We need to take advantage of [late static binding](https://www.php.net/manual/en/language.oop5.late-static-bindings.php) here and get the table name in the following way:
领英推荐
$table = with(new static())->getTable();
The query to get the results is fairly simple: it selects data based on the aforementioned $aggregateColumn’s content as date variable and sums up the number of IDs grouped by date. Note that the default variable used here is created_at, but any date/datetime variable can be used. This functionality can easily be extended to use non-date variables, which enables you to have a different grouping on the x-axis (eg. orders divided by different order types).
So now we have our data summed up and grouped by day, month or year - what do we do with it now? We add a controller, of course. As generalization of charts is our goal here, we are going to have a single controller action for fetching all of the charts for different models. Here is the complete code for the controller class:
app/Http/Controllers/ChartController.php
<?php
namespace App\Http\Controllers;
use App\Http\Resources\Resource;
use App\Util\Charts;
use Illuminate\Http\Request;
class ChartController extends Controller
{
public function getChartByTypeAndTimeDivision(
Request $request,
string $type,
string $timeDivision = 'month'
) : Resource
{
$class = Charts::getChartClass($type);
$timeDivisionString = Charts::getTimeDivision($timeDivision);
$methodName = 'get'.$timeDivisionString.'Aggregated';
$chart = $class::{$methodName}();
return new Resource($chart);
}
}
And the route:?
Route::get('/chart-by-type/{type}/{timeDivision}', [
ChartController::class,
'getChartByTypeAndTimeDivision',
]);
Charts class contains validation and formatting methods used to get class names and time division correct formatting based on request data. Here is it’s code:
app/Util/Charts.php
<?php
namespace App\Util;
use App\Models\Post;
use App\Models\User;
use Symfony\Component\HttpKernel\Exception\BadRequestHttpException;
class Charts
{
public static function getChartClass(string $type)
{
$types = [
'user' => User::class,
'post' => Post::class,
];
if (! isset($types[$type])) {
throw new BadRequestHttpException('Type not allowed!');
}
return $types[$type];
}
public static function getTimeDivision(string $timeDivision)
{
$timeDivisions = [
'month' => 'Monthly',
'year' => 'Yearly',
'day' => 'Daily',
];
if (! isset($timeDivisions[$timeDivision])) {
throw new BadRequestHttpException(
'Time division not allowed!');
}
return $timeDivisions[$timeDivision];
}
}
The getChartClass is used to get the class name of the chart type sent in the request. You will need to add each new type of chart after you add the Chartable interface and HasCharts trait to a new model for this to work. getTimeDivision method returns the formatted name of the time division sent in the request (eg. monthly -> Monthly). This formatted string is used to call the correct method from the HasCharts trait inside the ChartController. After we have information on the model and method name, we can call the method in the following way:
$class::{$methodName}();
In case we wanted a posts monthly chart, this expression would result to:
App/Models/Post::getMonthlyAggregated();
Response is formatted using a simple Resource class:
<?php
namespace App\Http\Resources;
use Illuminate\Http\Resources\Json\JsonResource;
class Resource extends JsonResource
{
? ? public function toArray($request)
? ? {
? ? ? ? return parent::toArray($request);
? ? }
}
And that concludes the whole implementation of simple generic charts in Laravel. The usage of the code from the frontend side is simple. In the route defined above:
/chart-by-type/{type}/{timeDivision}
type value can be whatever type you define in the getChartClass method of the Charts class. Valid values for time division are defined in the getTimeDivision of the same class, in this case: day, month, and year. Example frontend request and response:
Request: GET /api/chart-by-type/user/month
Response:
{
? "data": [
? ? 0 => {
? ? ? "date": "09-2022"
? ? ? "total": 23
? ? },
? ? 1 => {
? ? ? "date": "10-2022"
? ? ? "total": 11
? ? }
? ]
}
As mentioned before, this is just a basic charts implementation. It can be extended in multiple ways:
By removing the count() function from the query in the HasCharts trait, you will get a list of all data grouped by date, which can be useful in implementing different data tables.?