PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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');
Copy Clear
Copy Format Clear
<?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; } ) );
Show:  
Copy Clear