Laravel Collection Group By and Sum by Date

Question

I have a collection of data which are required to be processed to be able to generate report

below are the sample of data provided

$arrival_data = [
    [
        "arrival_date" => "09-Dec-2020",
        "total_amount" => "10",
        "total_traveller" => 6
    ],
    [
        "arrival_date" => "09-Dec-2020",
        "total_amount" => "20",
        "total_traveller" => 6
    ],
    [
        "arrival_date" => "10-Dec-2020",
        "total_amount" => "40",
        "total_traveller" => 3
    ]
];

I would like to process the data above so it will group by 'arrival_date' and sum the data so that the data will appear like below

$arrival_data = [
    [
        "arrival_date" => "09-Dec-2020",
        "total_amount" => "30", // sum of 10 + 20
        "total_traveller" => 6
    ],
    [
        "arrival_date" => "10-Dec-2020",
        "total_amount" => "40",
        "total_traveller" => 3
    ]
];

i haven't manage to produce the output intended using laravel collection, the closest i am able to get is as below

{
    "09-Dec-2020": 30,
    "10-Dec-2020": 40
}

using below code

$collection = collect($arrival_data);

$num = $collection->groupBy('arrival_date')->map(function ($row) {
    return $row->sum('total_amount');
});

return $num->toArray();

do laravel collection able to write codes to produce the output as intended or need to use a different method by looping array and processing.

Thank you

Answer 1

Instead of mapping to the sum, map the fields you want to keep to an array and sum the column and set it in the array. To get the static fields, fetch the first row, we can presume all the rows would be equal as they are grouped already. Then you will have the same array structure as you want to achieve.

$result = $collection->groupBy('arrival_date')
    ->map(function ($row) {
        $firstRow = $row->first();

        return [
            'arrival_date' => $firstRow['arrival_date'],
            'total_amount' => $row->sum('total_amount'),
            'total_traveller' => $firstRow['total_traveller'],
        ];
    })->values()
    ->all();

Group by will map the array keys to the grouped value, to avoid this and receive the expected results, just call ->values()->all() on the collection and it returns it values.

Answer 2

There might be a fancier way to do this with collections, but the first thing that comes to mind is a plain old foreach loop:

$arrival_data = [
    [
        'arrival_date' => '09-Dec-2020',
        'total_amount' => '10',
        'total_traveller' => 6
    ],
    [
        'arrival_date' => '09-Dec-2020',
        'total_amount' => '20',
        'total_traveller' => 6
    ],
    [
        'arrival_date' => '10-Dec-2020',
        'total_amount' => '40',
        'total_traveller' => 3
    ]
];

$grouped_arrival_data = [];

foreach ($arrival_data as $arrival_datum) {
    if (!isset($grouped_arrival_data[$arrival_datum['arrival_date']])) {
        $grouped_arrival_data['total_amount'] = (int) $arrival_datum['total_amount'];
        $grouped_arrival_data['total_traveller'] = $arrival_datum['total_traveller'];
        $grouped_arrival_data['arrival_date'] = $arrival_datum['arrival_date'];
    } else {
        $grouped_arrival_data['total_amount'] += (int) $arrival_datum['total_amount'];
        $grouped_arrival_data['total_traveller'] += $arrival_datum['total_traveller'];
    }
}