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');
<?php
var_export(
$db::table('plan')
->selectRaw('users.name, plan.option, SUM(plan.amount) amount')
->join('users', 'plan.user_id', '=', 'users.id')
->where(['plan.game_id' => 6])
->groupBy('name', 'option')
->get()
->toArray()
->reduce(
function($result, $row) {
extract($row);
$result['name']['option'] = ($result['name']['option'] ?? 0) + $amount;
return $result;
}
)
);