Laravel join tables and get max orders by users

Question

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

Answer 1

You can write this query as,

$privilaged_users = User::leftJoin('orders', 'orders.user_id',"=", 'users.id')
                    ->select('name','phone_number',DB::raw('max(orders.total) as orders_total'))
                    ->groupBy('orders.user_id')  
                    ->get();

As raw query, because by default it will consider whole string as column

EDIT

Your problem was, you were doing group by on field which is taken inside aggregate function.

Here are some links to explain my concern much better.

I have made changes in code. Please check once.