Laravel Collection with groupby, count and sum

Question

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.

Happy to go back to the database if needed, but assumed I could do this with the collection that I already have rather than create another query. Solutions I've found on here all appear to be providing a solution in the query.

Moretta

Answer 1

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]
]

Bettyannbettye

Answer 2

If dealing with collection methods, group by the opponent_ids, then loop over those groups to count the wins and losses by pluck()ing the result data and unpacking the countBy() result to overwrite the defaults then simply sum the points. To re-index the first level keys, chain values() and to convert to an array, chain toArray().

Notice that by default, map() provides the grouped rows as the first parameter and the grouping key as the second parameter.

If all groups were guaranteed to contain at least one win and one loss, then the zero defaults would not need to be declared.

Code: (PHPize Demo)

var_export(
    $coll
    ->groupBy('opposition_id')
    ->map(fn($group, $oppoId) => [
        'opposition_id' => $oppoId,
        'won' => 0,
        'lost' => 0,
        ...$group->pluck('result')->countBy(),
        'points' => $group->sum('points'),
    ])
    ->values()
    ->toArray()
);

Or (PHPize Demo)

var_export(
    $coll
    ->groupBy('opposition_id')
    ->map(fn($group, $oppoId) => [
        'opposition_id' => $oppoId,
        'won' => $group->sum(fn($row) => $row['result'] === 'won'),
        'lost' => $group->sum(fn($row) => $row['result'] === 'lost'),
        'points' => $group->sum('points'),
    ])
    ->values()
    ->toArray()
);

Result (from either script above):

array (
  0 => 
  array (
    'opposition_id' => 10,
    'won' => 2,
    'lost' => 1,
    'points' => 4,
  ),
  1 => 
  array (
    'opposition_id' => 11,
    'won' => 0,
    'lost' => 2,
    'points' => 0,
  ),
  2 => 
  array (
    'opposition_id' => 12,
    'won' => 1,
    'lost' => 1,
    'points' => 2,
  ),
)

---

If this collection data is coming from your database, then building a query will be more streamlined.

Code: (PHPize Demo)

var_export(
    $db::table('results')
    ->select('opponent_id')
    ->selectRaw("SUM(result = 'won') won")
    ->selectRaw("SUM(result = 'lost') lost")
    ->selectRaw("SUM(points) points")
    //->where(['id' => 1])
    ->groupBy('opponent_id')
    ->get()
    ->toArray()
);

This populates an array of objects with the same data as the previous snippet.

Kopp