I have 2 models Orders
and Users
, i want to get the list of users who ordered most with the sum of total as amount
The table structure
//users
id name phone_number
1 John 1111111111
2 Mike 2222222222
//orders
id user_id total
1 1 500
2 1 450
3 2 560
4 1 850
5 2 500
//expected result
name phone_number orders amount
John 1111111111 3 1800
Mike 2222222222 2 1060
Tried with following query
$privilaged_users = User::leftJoin('orders', 'orders.user_id', 'users.id')
->select('name','phone_number')
->max('orders.user_id as orders')
->groupBy('orders.user_id')
->get();
Getting Following error
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in
your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'as `orders`) as aggregate from `users` left
join `orders` on `orders`.`user_id` ' at line 1 (SQL: select
max(`orders`.`user_id` as `orders`) as aggregate from `users` left join `orders`
on `orders`.`user_id` = `users`.`id` where `users`.`deleted_at` is null)
thank you