How to get total values separated by each month in a single year on laravel and mysql?

Question

I am trying to sum up the total values for each month in a year but cannot do it correctly, it fetch the first value of each month and not the others.

$wastes = WasteCategory::with(['logReport' => function($log) use ($year) {
        $log->whereYear('sanitation_date_time', $year);
    }])->get();

    $months = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12];

    $allTotal = [];

foreach ($wastes as $waste) {
    foreach ($months as $month) {
        if (!array_key_exists($month, $allTotal)) {
            foreach ($waste->logReport as $log) {
                $logMonth = date('m', strtotime($log->sanitation_date_time));
                $sum = $log->where('waste_category', $waste->waste_category_name)->whereMonth('sanitation_date_time', $month)->sum('waste_weight');
                if ($logMonth == $month) {
                    $allTotal[$month] = [
                        $waste->waste_category_name => isset($allTotal[$month]) && !array_key_exists($waste->waste_category_name, $allTotal[$month]) ? $sum : 0
                    ];
                }
            }
        } else {
            foreach ($waste->logReport as $log) {
                $logMonth = date('m', strtotime($log->sanitation_date_time));
                if ($logMonth == $month) {
                    $allTotal[$month] = $log->where('waste_category', $waste->waste_category_name)->whereMonth('sanitation_date_time', $month)->sum('waste_weight');
                }
            }
        }
    }
}

What did I do wrong?

like https://stackoverflow.com/questions/40529355/laravel-eloquent-group-by-month-year use SQL to aggregate. Use `sum` rather than count. — danblack, Dec 18 '20 at 04:20

Donkarnash · Accepted Answer · 2020-12-18T07:37:06.577

From what I understand is that you want to have monthly totals of waste_weight grouped by waste_category_name.

An option would be

//Assuming that the name of model for logReport is LogReport
//And name of belongsTo relation on LogReport model is wasteCategory

$data = LogReport::with('wasteCategory')
    ->whereYear('sanitation_date_time', $year)
    ->get()
    ->groupBy([
        fn($logReport) => Carbon::parse($logReport->sanitation_date_time)->format('Y-M'),
        fn($logReport) => $logReport->wasteCategory->waste_category_name
    ])
    ->map(fn($records, $month) => $records->map->sum('waste_weight')->all())
    ->all();

//$data should be something like
[
    '2020-Dec' => [
        'Waste1' => 450,
        'Waste2' => 265,
        'Waste3' => 764,
        'Waste4' => 357,
    ],
    '2020-Nov' => [
        'Waste1' => 210,
        'Waste2' => 678,
        'Waste3' => 354,
        'Waste4' => 750,
    ],
    //...
]

the values for all the wastes are same – Lal Tetea Dec 18 '20 at 07:12 — Lal Tetea, Dec 18 '20 at 07:12
@LalTetea Have updated the answer - check and let me know – Donkarnash Dec 18 '20 at 07:37 — Donkarnash, Dec 18 '20 at 07:37

How to get total values separated by each month in a single year on laravel and mysql?

1 Answers1