Group by two columns then sum another column within each group with Laravel Eloquent

Question

After querying two tables using Laravel, I'm trying to group the results by two columns and sum a third column within each group.

Here is a minimal schema with sample data to support my question:

CREATE TABLE plan (id int, user_id int, game_id int, amount int, `option` varchar(100));
INSERT INTO plan (id, user_id, game_id, amount, `option`)
VALUES
(1, 1, 6, 10, 'option1'),
(2, 1, 6, 12, 'option1'),
(3, 2, 6, 10, 'option1'),
(4, 2, 6, 12, 'option1'),
(5, 2, 6, 5, 'option2'),
(6, 2, 6, 6, 'option2');

CREATE TABLE users (id int, name varchar(100));
INSERT INTO users (id, name)
VALUES
(1, 'username1'),
(2, 'username2');

My current code: (PHPize Demo)

$game = (object) ['id' => 6];

var_export(
    $db::table('plan')
    ->select('plan.amount', 'plan.option', 'users.name')
    ->join('users', 'plan.user_id', '=', 'users.id')
    ->where(['plan.game_id' => $game->id])
    ->get()
    ->groupBy('name')
    ->map(function ($option) {
        return $option
            ->groupBy('option');
    })
    ->toArray()
);

Current result:

array (
  'username1' => 
  array (
    'option1' => 
    array (
      0 => 
      (object) array(
         'amount' => 12,
         'option' => 'option1',
         'name' => 'username1',
      ),
      1 => 
      (object) array(
         'amount' => 10,
         'option' => 'option1',
         'name' => 'username1',
      ),
    ),
  ),
  'username2' => 
  array (
    'option2' => 
    array (
      0 => 
      (object) array(
         'amount' => 6,
         'option' => 'option2',
         'name' => 'username2',
      ),
      1 => 
      (object) array(
         'amount' => 5,
         'option' => 'option2',
         'name' => 'username2',
      ),
    ),
    'option1' => 
    array (
      0 => 
      (object) array(
         'amount' => 12,
         'option' => 'option1',
         'name' => 'username2',
      ),
      1 => 
      (object) array(
         'amount' => 10,
         'option' => 'option1',
         'name' => 'username2',
      ),
    ),
  ),

I need to sum the "amount" of each second-level "option" group.

I need this reduced result structure:

array (
  'username1' => 
  array (
    'option1' => 22
  ),
  'username2' => 
  array (
    'option2' => 11
    'option1' => 22
  ),

Answer 1

You can use the laravel reduce() method https://laravel.com/docs/5.7/collections#method-reduce.

Inside your map function add in reduce after the group by like so:

->map(function ($option) {
    return $option
        ->groupBy('option')
        ->map(function($group) {
           // Loop through each group and reduce them.
           $group->reduce(function($carry, $item) {
                // Assume that we always want the last value by using php end() function on array.
                return $carry + end($item);
           }, 0);
        });

});

I haven't tested this so if there are issues let me know and I'll update the answer.