Example 4. GROUP BY Multiple Tables

In the query, you count the number of purchased unique products. Obviously, if you are trying to calculate the total number of purchased products, then you need to sum up the quantity and make a left join.

$orders = DB::table('orders')
    ->selectRaw(
        'users.name as user_name, sum(orders.total) as total, sum(order_product.quantity) as total_products'
    )
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->join('order_product', 'order_product.order_id', '=', 'orders.id', 'left')
    ->groupBy('user_name')
    ->get();