need help for creating a complex query (orders, users)

I have two tables orders and users.

I want to create a query builder to get an array like this:

[
	['first_name' = 'Test', 'last_name' = 'User', 'number_of_orders' = 16, 'orders_value' = 7580],
    ...
]

the first 10 entries order by 'orders_value' desc.

I have made it work like taking all the users (there are over 1000 users) and itereate each user in orders to calc the sum and nr of orders .... it's very slow

I am trying to make a single DB query:

        $orders = DB::table('orders')
            ->whereNull('deleted_at')
            ->join('users','users.id','=','orders.UserID')
            ->selectRaw('sum(Value + Shipping) AS OrderValue')
            ->groupBy('users.id')
            ->orderBy('OrderValue', 'desc')
            ->limit(10)
            ->get();

and I am getting only the OrderValue.

How can I add to the query: users.firstName, users.lastName, and the count of each user order (how many orders the user made it) ?

If I try to add more fields using ->select() it gives an error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_db.orders.Value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

calin.ionut

5 years ago

Level 5

I have changed the query and now works.

 $orders = DB::table('orders')
            ->whereNull('deleted_at')
            ->join('users','users.id','=','orders.UserID')
            ->select('users.id', 'users.firstName', 'users.lastName')
            ->selectRaw('sum(Value + Shipping) AS OrderValue')
            ->selectRaw('count(id) AS NumberOrders')
            ->groupBy('users.id')
            ->orderBy('OrderValue', 'desc')
            ->limit(10)
            ->get();

Please or to participate in this conversation.