SQL fill in empty months if data isn't present in Laravel 8 query

user_9982090 0
Viewed 279

I'm using the query builder in my Laravel 8 project to create a monthly sum of all of the deleted users in my application, I'm then outputting two items to use as part of a graph, total and date.

This works well, but, if a month didn't have any data then it would skip straight onto the next month, e.g:

  • 2021-01
  • 2021-04
  • 2021-05

How can I modify the query to add all of the months, from a given start date, up until "now" and effectively add blank values for those months that don't have data?

My current query is:

$data = User::selectRaw('DATE_FORMAT(created_at, "%Y-%m") as date, COUNT(*) as total')
                ->groupByRaw('DATE_FORMAT(created_at, "%Y-%m")')
                ->withTrashed()
                ->whereNotNull('deleted_at')
                ->get();

And I'm thinking of calculating the start by doing something like this:

$user = User::orderBy('created_at', 'asc')->first();
$start = $user->created_at;

$data = User::selectRaw('DATE_FORMAT(created_at, "%Y-%m") as date, COUNT(*) as total')
                ->groupByRaw('DATE_FORMAT(created_at, "%Y-%m")')
                ->withTrashed()
                ->whereNotNull('deleted_at')
                ->get();

$end = Carbon::now()->endOfMonth();

Not sure how to get it into the query though

query-builder laravel php
1 Answers

The problem here is that groups originate from the rows, not the other way around. A group will not exist unless a row exists to be included within the group. You only see "missing" months because, in your mind, there are months between January and April.

I'd recommend doing it in post-processing, because any clever attempts to create phantom rows so that groups appear will inevitably be more complicated and more frustrating to maintain.

It may feel clunky, but looping through months from Carbon and adding values to your query result will work fine. Plus, you don't need to rely on $start from your first user result, you can set it yourself.

$start = Carbon::today()->subYear(); // Use any start date, even include it from user input (like a datepicker).

$end = Carbon::today(); // Use any end date, though it won't be useful any later than today.

$loop = $start->copy();

while ($loop->lessThanOrEqualTo($end)) {
    $exists = $data->first(function($item) use ($end) {
        return $item->date == $end->format('Y-m');
    });

    if (!$exists) {
        $row = new stdClass();
        $row->date = $loop->copy()->format('Y-m');
        $row->total = 0;
        $data->push($row);
    }

    $loop->addMonth(); // This keeps the loop going.
}

This accomplishes what you want and doesn't get into any N+1 issues.

Edit: Added example below in re-usable function.

function fillEmptyMonths(Collection $data, Carbon $start, Carbon $end): Collection
{

    $loop = $start->copy();

    // Loop using diff in months rather than running comparison over and over.
    for ($months = 0; $months <= $start->diffInMonths($end); $months++) {

        if ($data->where('date', '=', $loop->format('Y-m'))->isEmpty()) {
            $row = new stdClass();
            $row->date = $loop->copy()->format('Y-m');
            $row->total = 0;
            $data->push($row);
        }

        $loop->addMonth();
    }
    
    return $data;
}

You could also expand this to take another parameter that defines the increment (and pass it "month", "day", "year", etc.). But if you are only using month, this should work.

user_143815550
Related