I'm struggling to get a groupby on a collection to work - I'm not getting the concept just yet.
I'm pulling a collection of results from a table for a player the eloquent collection will have data like this:
['player_id'=>1, 'opposition_id'=>10, 'result'=>'won', 'points'=>2],
['player_id'=>1, 'opposition_id'=>11, 'result'=>'lost', 'points'=>0],
['player_id'=>1, 'opposition_id'=>12, 'result'=>'lost', 'points'=>0],
['player_id'=>1, 'opposition_id'=>10, 'result'=>'won', 'points'=>2],
['player_id'=>1, 'opposition_id'=>11, 'result'=>'lost', 'points'=>0],
['player_id'=>1, 'opposition_id'=>10, 'result'=>'lost', 'points'=>0],
['player_id'=>1, 'opposition_id'=>12, 'result'=>'won', 'points'=>2],
I want to be able to groupBy('opposition_id')
and then give me a count of results in total, total won, total lost and sum of points to end up with a collection like this:
['opposition_id'=>10, 'results'=>3, 'won'=>2, 'lost'=>1, 'points'=>4],
['opposition_id'=>11, 'results'=>2, 'won'=>0, 'lost'=>2, 'points'=>0],
['opposition_id'=>10, 'results'=>2, 'won'=>1, 'lost'=>1, 'points'=>2]
I'm trying to avoid going back to the database to do this as I already have the results from previous activity.
How can I do this using Laravel collection methods, So far all I have is:
$stats = $results->groupBy('opposition_id');
I've looked at map()
but do not yet understand that method to work through a solution
Can anyone point me in the right direction please.
Happy to go back to the database if needed but assumed I could do this with the collection I already have rather than create another query. Solutions I've found on here all appear to be providing a solution in the query
Thank you
Take a look here, working code with explanation in comments.
// make a collection
$c = collect(
[
['player_id' => 1, 'opposition_id' => 10, 'result' => 'won', 'points' => 2],
['player_id' => 1, 'opposition_id' => 11, 'result' => 'lost', 'points' => 0],
['player_id' => 1, 'opposition_id' => 12, 'result' => 'lost', 'points' => 0],
['player_id' => 1, 'opposition_id' => 10, 'result' => 'won', 'points' => 2],
['player_id' => 1, 'opposition_id' => 11, 'result' => 'lost', 'points' => 0],
['player_id' => 1, 'opposition_id' => 10, 'result' => 'lost', 'points' => 0],
['player_id' => 1, 'opposition_id' => 12, 'result' => 'won', 'points' => 2]
]
);
// this only splits the rows into groups without any thing else.
// $groups will be a collection, it's keys are 'opposition_id' and it's values collections of rows with the same opposition_id.
$groups = $c->groupBy('opposition_id');
// we will use map to cumulate each group of rows into single row.
// $group is a collection of rows that has the same opposition_id.
$groupwithcount = $groups->map(function ($group) {
return [
'opposition_id' => $group->first()['opposition_id'], // opposition_id is constant inside the same group, so just take the first or whatever.
'points' => $group->sum('points'),
'won' => $group->where('result', 'won')->count(),
'lost' => $group->where('result', 'lost')->count(),
];
});
// if you don't like to take the first opposition_id you can use mapWithKeys:
$groupwithcount = $groups->mapWithKeys(function ($group, $key) {
return [
$key =>
[
'opposition_id' => $key, // $key is what we grouped by, it'll be constant by each group of rows
'points' => $group->sum('points'),
'won' => $group->where('result', 'won')->count(),
'lost' => $group->where('result', 'lost')->count(),
]
];
});
// here $groupwithcount will give you objects/arrays keyed by opposition_id:
[
10 => ["opposition_id" => 10,"points" => 4,"won" => 2,"lost" => 1]
11 => ["opposition_id" => 11,"points" => 0,"won" => 0,"lost" => 2]
12 => ["opposition_id" => 12,"points" => 2,"won" => 1,"lost" => 1]
]
// if you use $groupwithcount->values() it'll reset the keys to 0 based sequence as usual:
[
0 => ["opposition_id" => 10,"points" => 4,"won" => 2,"lost" => 1]
1 => ["opposition_id" => 11,"points" => 0,"won" => 0,"lost" => 2]
2 => ["opposition_id" => 12,"points" => 2,"won" => 1,"lost" => 1]
]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With