Level 16
flatMap it instead of plucking.
// omitted
->get()
->flatMap->cases
->sum()
Dec 9, 2021
10
Level 10
Best way to reduce multiple collections
Hi, I am looking to get the sum of values from a number of collections. If I do
foreach ($productions as $production) {
$productionCases = ProductionCase::where('production_id', $production->id)
->where('month', '>', $request->month)
->get()
->pluck('cases');
}
it returns
0: (17) [15, 20, 10, 0, 12, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
1: (17) [4, 5, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
I was trying to use array_reduce but realized I am getting collections back. How do I reduce it so I get a single array with
(17) [19, 25, 16, 0, 12, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
Level 10
@mabdullahsari I am not sure putting a flatmap inside of the foreach would work, will it?
Level 104
@chrisgrim what are you looking to achieve here? Immediately I see queries inside a loop; I question the implementation...
Level 10
@tykus Each Production -> hasMany ProductionCases. So for a year, I am trying to get the sum of the cases from each production.
Each production cases look like
0: {id: 32, production_id: 3, cases: 15, month: '2021-08-01 00:00:00', created_at: '2021-12-09T19:59:22.000000Z', …}
1: {id: 33, production_id: 3, cases: 20, month: '2021-09-01 00:00:00', created_at: '2021-12-09T19:59:22.000000Z', …}
2: {id: 34, production_id: 3, cases: 10, month: '2021-10-01 00:00:00', created_at: '2021-12-09T19:59:22.000000Z', …}
3: {id: 35, production_id: 3, cases: 0, month: '2021-11-01 00:00:00', created_at: '2021-12-09T19:59:22.000000Z', …}
4: {id: 36, production_id: 3, cases: 12, month: '2021-12-01 00:00:00', created_at: '2021-12-09T19:59:22.000000Z', …}
5: {id: 37, production_id: 3, cases: 10, month: '2022-01-01 00:00:00', created_at: '2021-12-09T19:59:22.000000Z', …}
and so on...
I want to combine the cases for each month for each production. So production 1 has 15 cases for the first month. Production 2 has 4 cases for the first month. Thus the returned collection would look like (17) [19, 25, 16, 0, 12, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] with 15 + 4 meaning 19 total cases for the first month.
Level 104
@chrisgrim you should be able to achieve all of this in a query:
Production::selectRaw('DATE_FORMAT(month, "%Y-%m") as month, SUM(cases) as sum_cases')
->join('production_cases', 'productions.id', 'production_cases.production_id')
->where('month', '>', $request->month)
->groupByRaw('DATE_FORMAT(month, "%Y-%m")')
->get()
Level 10
@tykus That worked! Though I will admit it is very hard for me to understand what is happening with Raw. I think I will have to do some tutorials on selectRaw. Thanks!!!
Level 104
@chrisgrim it is simply grouping the records by the month from production_cases; but, it formats the date into YYYY-MM format to ensure any records within each month are counted. This step is unnecessary if the month is always YYYY-MM-01 00:00:00:
Production::selectRaw('month, SUM(cases) as sum_cases')
->join('production_cases', 'productions.id', 'production_cases.production_id')
->where('production_cases.month', '>', $request->month)
->groupBy(production_cases.month)
->get()
Level 122
bear in mind that months are not unique, they occur every year. You might end up with Nov-22 counts including Nov-21
Level 104
@Snapey the month column actually has a full date according to the data shared by the OP
0: {id: 32, production_id: 3, cases: 15, month: '2021-08-01 00:00:00', created_at: '2021-12-09T19:59:22.000000Z', …}
Level 10
@snapey Thanks!
Please or to participate in this conversation.