John Davidson

Message:

I've got a query in my Laravel project that returns a collection of data from my database between two dates, it's then grouped by the hour (but I can change this to my liking), I'd now like to return the "dates" for data before they existed in the database as a way of building up a list of dates that I can display in my web page, eve though there will be no data for these days, my chart would look consistent.

As an example:

• I want to see the past 30 days of data as a history, but the first 20 days of this period have no data yet, I still need to return the date keys for these days, except with just no data in them.

My current code is as follows (I'm using Carbon):

$uptimeData = UptimeChecks::where('user_id', 1)

                        ->where('monitor_id', 1)

                        ->where('checked_at', '>=', '2021-01-01 00:00:00')

                        ->where('checked_at', '<=', '2021-01-30 23:59:59')

                        ->orderBy('checked_at', 'asc')

                        ->select('event', 'checked_at')

                        ->get();



$from = Carbon::now()->subDays(60);

$period = CarbonPeriod::create($from, '2021-01-30 23:59:59');

$dates = $period->toArray();



foreach ($dates as $key => $date) {

  $dates[$key] = Carbon::parse($date)->format('Y-m-d');

}



$uptimeData = collect($uptimeData);

$uptimeData = $uptimeData->merge($dates);



$uptimeDataTimeline = $uptimeData->groupBy(function ($item, $key) {



  if (isset($item->checked_at)) {

    $date = Carbon::parse($item->checked_at);

  } else {

    $date = Carbon::parse($item);

  }



  return $date->format('Y-m-d');

});

Even though there would be no entries to display a "checked_at" column, can this be spoofed with the date for that day with no data?

Update 03/05 @ 20:30

I've updated my description to reflect the latest attempt to solve this problem, I appear to have constructed what I need, however, have some issues:

1. Where I'm looping over my $dates is there a way to build up some structure within each item so I don't have to do if/else checks on all my variables and keys?


2. For some reason, the "fake" dummy dates are being added after the real data, e.g: 1st Jan, 2nd Jan, 30th Dec ... how can I reverse this?

---

My Answer:
days where no real data exists yet as well. One way to achieve this is by creating a dummy data array for the days where no real data exists and merging it with the existing data collection. Here's an example of how you can do this:

php
// Get real data from database between two dates
$realData = YourModel::whereBetween('created_at', [$startDate, $endDate])
->get();

// Group real data by day
$groupedRealData = $realData->groupBy(function ($item) {
return $item->created_at->format('Y-m-d');
});

// Create dummy data for days where no real data exists
$dummyData = collect();
$startDate = Carbon::parse($startDate);
$endDate = Carbon::parse($endDate);

while ($startDate <= $endDate) {
$date = $startDate->format('Y-m-d');

if (!$groupedRealData->has($date)) {
$dummyData->push([
'date' => $date,
'dummy_data' => 'Your dummy data here'
]);
}

$startDate->addDay();
}

// Merge real data and dummy data
$mergedData = $groupedRealData->merge($dummyData);

// Return the merged data
return $mergedData;


In this example, we first get the real data from the database between the specified dates and group it by day. We then create a dummy data array for the days where no real data exists by iterating through each day between the start and end dates and checking if there is no real data for that day. Finally, we merge the real data and dummy data collections and return the merged data.

You can customize the dummy data array and the grouping logic according to your requirements.