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

Rahul · Accepted Answer · 2017-09-06T12:39:08.370

4

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.

edited Sep 06 '17 at 12:39

answered Sep 06 '17 at 12:04

Rahul

18,271
7
41
60

thank you for your response, tried your solution getting the same old `SQLSTATE[42000]: Syntax error or access violation` error – Mr Robot Sep 06 '17 at 12:06
yes i did same error `Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ideal_chicken.users.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `name`, `phone_number`, max(orders.user_id) as orders from `users` left join `orders` on `orders`.`user_id` = `users`.`id` where `users`.`deleted_at` is null group by `orders`.`user_id`)` – Mr Robot Sep 06 '17 at 12:07
try to change alias name like orders_total – Rahul Sep 06 '17 at 12:09
1

try to change alias name like orders_total and try to remove this only_full_group_by mode from sql-mode in my.ini of mysql folder – Rahul Sep 06 '17 at 12:14
there is no `only_full_group_by ` in my **my.ini** file, i ran the code in production server the error is same – Mr Robot Sep 06 '17 at 12:19
*select name, phone_number, max(orders.user_id) as orders_total from users left join orders on orders.user_id = users.id where users.deleted_at is null group by orders.user_id* try running this query on mysql tool like phpmyadmin or something – Rahul Sep 06 '17 at 12:22
`Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ideal_chicken.users.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – Mr Robot Sep 06 '17 at 12:31
I got your problem. [link](https://stackoverflow.com/questions/45738926/laravel-eloquent-query) and [link1](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) We were doing group by on field which is taken inside aggregate function. I hope I am clear. I changed code. And here is the query *select name, phone_number, max(orders.total) as orders_total from users left join orders on orders.user_id = users.id where users.deleted_at is null group by orders.user_id* Give it a try this should work – Rahul Sep 06 '17 at 12:40
once check my edit portion of answer you will get it – Rahul Sep 06 '17 at 12:42
thank you, the code works only if i change `'strict' => true,` to `false`. dose it affect to project – Mr Robot Sep 06 '17 at 12:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/153783/discussion-between-rahul-meshram-and-mr-robot). – Rahul Sep 06 '17 at 12:46

ishegg · Answer 2 · 2017-09-06T12:13:57.157

1

SELECT MAX(field AS something) is invalid syntax. Try this:

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

And refer to the MAX field as "aggregate"

edited Sep 06 '17 at 12:13

answered Sep 06 '17 at 12:03

ishegg

9,685
3
16
31

thank you for your response, tried your code getting `Call to a member function groupBy() on string ` – Mr Robot Sep 06 '17 at 12:05
Try now, please. – ishegg Sep 06 '17 at 12:14
getting `Syntax error or access violation: 1055 'eitansof_ideal_chicken.users.name' isn't in GROUP BY (SQL: select `name`, `phone_number`, max(orders.user_id) AS orders_total, `orders`.`user_id` from `users` left join `orders` on `orders`.`user_id` = `users`.`id` where `users`.`deleted_at` is null group by `orders`.`user_id`)` – Mr Robot Sep 06 '17 at 12:16
Oh, you have your `sqlmode` as `ONLY_FULL_GROUP_BY`. You need to either change the query as to place any column your `SELECT`ing in the `GROUP BY` clause, or set `strict => false` in the `mysql` array in `config/database.php` – ishegg Sep 06 '17 at 12:22

Laravel join tables and get max orders by users

2 Answers2