To achieve the desired output directly from the query, you would need to generate a series of months and then left join your prints table to this series. Unfortunately, SQL doesn't have a built-in way to generate a series of dates or months, so you'll need to create it manually or use a stored procedure if your database supports it.
However, since you're using Laravel, you can create an array of months in PHP and then loop through this array to fetch the data for each month. This way, you can ensure that you get a result for each month, even if it's zero.
Here's how you can do it:
use Carbon\Carbon;
// Assuming $qtd_months is defined and is the number of months you want to go back
$end = Carbon::now()->endOfDay();
$start = Carbon::now()->subMonths($qtd_months - 1)->startOfDay();
// Generate an array of months
$months = collect();
for ($month = $start->copy(); $month->lte($end); $month->addMonth()) {
$months->push($month->format('n-Y'));
}
// Query the database for each month and build the result set
$results = $months->mapWithKeys(function ($month) use ($start, $end) {
$monthStart = Carbon::createFromFormat('n-Y', $month)->startOfMonth();
$monthEnd = Carbon::createFromFormat('n-Y', $month)->endOfMonth();
// Adjust the start and end dates if they exceed the range we're looking at
if ($monthStart->lt($start)) {
$monthStart = $start;
}
if ($monthEnd->gt($end)) {
$monthEnd = $end;
}
$total = DB::table('prints')
->whereBetween('print_date', [$monthStart, $monthEnd])
->sum(DB::raw('pages * copies'));
return [$month => $total];
});
// Format the results as desired
$finalResults = $results->map(function ($total, $month) {
return ['month' => $month, 'total' => $total];
})->values();
// Now $finalResults contains the desired output
This code snippet will give you a collection of results with a total for each month, including months with zero prints. It's not as efficient as doing it all in a single query, but it's a straightforward solution that works within the constraints of Laravel and MySQL.
