mercredi 6 septembre 2017

Laravel join tables and get max max orders by users

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     1      500

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('name');

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




Aucun commentaire:

Enregistrer un commentaire