This article will teach you how to generate a log view with daily number of incidents.

Requirements

The guide is written Laravel in mind, so functions such as collect() and today() are expected to be present.

🤔 Problem

We want to display both days with and without incidents, but the database of course don't contain records of days without incidents.

The solution to this problem is first creating a range with CarbonPeriod.

📆 Creating the range

The range, or period, isn't a hard job when using CarbonPeriod. It generates an array with the range by supplying a start and finish date.

Syntax

$period = CarbonPeriod::create($start, $finish);

Example

$period = collect(CarbonPeriod::create(today()->subDays(7), today()));

Note, we encapsulated it as a collection to take advantage of these features later

📂 Gather log data

Given almost all tables in Laravel has a created_at column, this would be a logic measurement for this article.

Example

The snippet will output all records created within the last 7 days ordered in chronological order and grouped by date.

$dbLog = Log::where('created_at', today())
  ->subDays(7))
  ->selectRaw('count(*) as incidents, DATE(created_at) as `date`')
    ->orderBy('id', 'asc')
  ->groupBy(DB::raw('DATE(created_at)'))
  ->get();

🧚‍♂️ Combining the range and log data

The last thing we need to do is merging it together. Here we can benefit from $period being a collection.

$log = collect([]);

$period->each(function($date) use ($dbLog, &$log) {
  $day = new \stdClass();
  $day->incidents = $log->where('date', $date->toDateString())->first()->incidents ?? 0;
  $day->date = $date;
  $log->push($day);
});

###🏁 All together

public function dailyLogView(): Collection
{
  $period = collect(CarbonPeriod::create(today()->subDays(7), today()));
  $log = collect([]);

  $dbLog = Log::where('created_at', today())
    ->subDays(7))
    ->selectRaw('count(*) as incidents, DATE(created_at) as `date`')
    ->orderBy('id', 'asc')
    ->groupBy(DB::raw('DATE(created_at)'))
    ->get();

  $period->each(function($date) use ($dbLog, &$log) {
    $day = new \stdClass();
    $day->incidents = $log->where('date', $date->toDateString())->first()->incidents ?? 0;
    $day->date = $date;
    $log->push($day);
  });

  return $log;
}