Laravel多关系与sum和count排序连接在一起

Question

I'm attempting to create a small system to manage my company exporting department, I'm trying to join 3 tables listed below:

mysql> select id,code,name,country from customers limit 10;
+----+-------+--------------------------------------+-----------------------------+
| id | code  | name                                 | country                     |
+----+-------+--------------------------------------+-----------------------------+
|  1 | t1584 | Swift Group                          | Tuvalu                      |
|  2 | k5583 | Brown, Hauck and Wolff               | Bhutan                      |
|  3 | o8481 | Mueller-Barrows                      | Haiti                       |
|  4 | e176  | Waters-Anderson                      | Mozambique                  |
|  5 | e3276 | Stehr, Lockman and Jacobs            | Congo                       |
|  6 | w69   | Howell PLC                           | French Southern Territories |
|  7 | o881  | Skiles-Fritsch                       | France                      |
|  8 | x2925 | Boyle-Haag                           | Uganda                      |
|  9 | k6534 | Macejkovic, Satterfield and Tremblay | South Africa                |
| 10 | y773  | Morissette Inc                       | United Kingdom              |
+----+-------+--------------------------------------+-----------------------------+
10 rows in set (0.00 sec)

mysql> select id,code,customer_id from orders limit 30;
+----+----------+-------------+
| id | code     | customer_id |
+----+----------+-------------+
|  1 | ORD67703 |           1 |
|  2 | ORD84022 |           1 |
|  6 | ORD29040 |           2 |
|  7 | ORD70298 |           2 |
|  9 | ORD40493 |           3 |
| 10 | ORD89299 |           3 |
| 11 | ORD35719 |           3 |
| 12 | ORD17115 |           4 |
| 18 | ORD62357 |           5 |
| 21 | ORD70979 |           5 |
| 22 | ORD20614 |           6 |
| 23 | ORD90549 |           6 |
| 24 | ORD95016 |           6 |
| 25 | ORD11464 |           6 |
| 26 | ORD39202 |           6 |
| 27 | ORD42648 |           6 |
| 28 | ORD72554 |           7 |
| 29 | ORD74224 |           8 |
| 30 | ORD59620 |           9 |
+----+----------+-------------+
30 rows in set (0.00 sec)

mysql> select id,order_id,customer_id,item_id,quantity from order_items limit 300;
+-----+----------+-------------+---------+----------+
| id  | order_id | customer_id | item_id | quantity |
+-----+----------+-------------+---------+----------+
|   1 |        1 |           1 |       8 |   143000 |
|   2 |        1 |           1 |      27 |   143000 |
|   3 |        1 |           1 |      13 |    85800 |
...........
...........
+-----+----------+-------------+---------+----------+
300 rows in set (0.00 sec)

I'm trying to make html table with form to filtering and sorting the below data

1. company code
2. company name
3. company country
4. company orders = count of
5. company orders quantities = sum of company orders items

My Code Get Data

$items = new Customer();
$items->withCount('orders');
$items->with('ordersItems');

Filtering Not working with all columns

if (Input::has('code')) {
   $items->where('code', '=', Input::get('code'));
}

Ordering / sorting working with customers table columns and orders count only

$items->orderBy($request->order->column', $request->order->dir);

Limits - working well

$items->limit($request->length)->offset($request->start);
$items->groupBy('customers.id');
$items->get();

Output data

$item->code,
$item->name,
$item->country,
$item->orders_count,
$item->ordersItems()->sum('quantity'),

Update :

    $orders = DB::table('orders')
        ->select('orders.customer_id', DB::raw('COUNT(orders.customer_id) as orders_count'))
        ->groupBy('orders.customer_id');

    $quantity = DB::table('order_items')
        ->select('customer_id', DB::raw('SUM(quantity) as quantities'))
        ->groupBy('customer_id');

    $items = DB::table('customers')
        ->select(['customers.*',
            DB::raw('COALESCE(orders_count, 0) AS orders_count'),
            DB::raw('COALESCE(quantities, 0) AS quantities')])
        ->leftJoinSub($orders, 'orders', function ($join) {
            $join->on('customers.id', '=', 'orders.customer_id');
        })->leftJoinSub($quantity, 'order_items', function ($join) {
            $join->on('customers.id', '=', 'order_items.customer_id');
        });