Laravel query get dummy data for days where no real data exists yet

Question

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:

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?
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?

You could build a collection keyed with all the dates you want (potentially with default values if it's useful for the view) and then merge the results of the query with it. — mikenewbuild, May 03 '21 at 18:14
On my phone, so difficult to type it out, here's how to build a range of dates https://stackoverflow.com/a/50854594 and here's the merge method you can use on collections https://laravel.com/docs/8.x/collections#method-merge — mikenewbuild, May 03 '21 at 18:22
You should only fetch the data which actually exist in the database. For your scenario, you can write code at controller where you can add empty date to make your required format. — Rohit Mittal, May 03 '21 at 18:24
@mikenewbuild I've updated my description with what I believe you're referring to, but I'm not getting anything back in my array now? What am I missing? — Ryan H, May 03 '21 at 18:40
The merge method will replace existing keys, so build the dates from date period first (you will have a key for every day). Then run your query and key by dates with groupBy (this will have some of the days). Then you can do `collect($dates)->merge($uptimeData)` to populate the days with data from your query. — mikenewbuild, May 03 '21 at 19:43
Isn't that like my revised description? See "Update 03/05 @ 20:30" and the revised code just above that? — Ryan H, May 03 '21 at 19:48
Hey Ryan, you were getting close, but you need to build the dates *first* then merge in the query results. The merged data will replace any existing keys (that's why it was overwriting your data in your updated example). I've added an answer with an example of code that would produce the results I think you want. — mikenewbuild, May 03 '21 at 22:41

mikenewbuild · Accepted Answer · 2021-05-03T23:34:08.487

1

One way to achieve this is by creating a collection of the dates for the time period you want, and then merging the results of a query grouped by date into it.

Here's an example:


$from = '2021-01-01';
$to = '2021-01-31';

$period = CarbonPeriod::create($from, $to);
$dates = collect($period->toArray())->mapWithKeys(function ($date) {
  return [$date->format('Y-m-d') => []];
});

// $dates = ['2021-01-01' => [], '2021-01-02' => [], ...etc]

$uptimeChecks = UptimeChecks::query()
    ->where('user_id', 1)
    ->where('monitor_id', 1)
    ->whereBetween('checked_at', [$from, $to])
    ->orderBy('checked_at', 'asc')
    ->select('event', 'checked_at')
    ->get();

$uptimeDates = $uptimeChecks->groupBy(function ($item, $key) {
  return $item->checked_at->format('Y-m-d');
});

// $uptimeDates = ['2021-01-02' => ['event1', 'event2'], ...etc]

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

// $uptimeData = ['2021-01-01' => [], '2021-01-02' => ['event1', 'event2'], ...etc]

This assumes you are casting the checked_at field to a date in your UptimeChecks Model.

edited May 03 '21 at 23:34

answered May 03 '21 at 22:39

mikenewbuild

148
10

1

NP, glad it solved it! Ps I noticed your `UptimeChecks` model is plural but the convention is for it to be the singular `UptimeCheck` – mikenewbuild May 04 '21 at 18:55
Yeah, that's because when you're getting multiple records back from the DB, it seems logical to go `UptimeChecks::where()->get()` for instance, where as a singular sounds suited to a single uptime check like `UptimeCheck::first()`, what's your thoughts? – Ryan H May 04 '21 at 19:23
A model is (roughly) designed to represent a single row in a database, if you use `first()` you'll get an instance of `UptimeCheck`, when you run `get()` you get a `Collection` (not a model) which can contain none, or multiple instances of an `UptimeCheck`, so I would always use singular for the model. – mikenewbuild May 04 '21 at 19:36

Laravel query get dummy data for days where no real data exists yet

Update 03/05 @ 20:30

1 Answers1