Laravel eloquent query groupBy category with sum and sortBy sum

I have 3 related tables:

Table category :

+-----+---------------+
| id  | nama_kategori |
+-----+---------------+
| 1   | Sayur         |
| 2   | daging        |
| 3   | buah          | 
+-----+---------------+

Table Barang

+-----+-------------+-------------+
| id  | nama_barang | id_kategori |
+-----+-------------+-------------+
| 1   | Sayur 1     | 1           |  
| 2   | Sayur 2     | 1           |   
| 3   | Daging Ayam | 2           |   
| 4   | Apple       | 3           |   
| 5   | Banana      | 3           |      
+-----+-------------+-------------+

Table barang_keluar

+-----+-------------+-----------+---------+
| id  | tgl_keluar  | id_barang | jml_bk  |
+-----+-------------+-----------+---------+
| 1   | 02-03-2021  | 1         |   200   |
| 2   | 04-03-2021  | 2         |   100   |
| 3   | 12-03-2021  | 1         |   100   |
| 4   | 15-03-2021  | 3         |   500   |
| 5   | 20-03-2021  | 2         |   100   |
| 6   | 25-03-2021  | 1         |   100   |
| 6   | 25-03-2021  | 4         |   100   |
| 6   | 25-03-2021  | 5         |   100   |
+-----+-------------+-----------+---------+

The query I'm making now looks like this:

$kategorix='All';
$bulan = '2021-03';
$bulanx=Carbon::parse($bulan)->format('Y-m');
    
$kategoriy=DB::table('barang_keluar')->join('barang','barang.id','=','barang_keluar.id_barang')
            ->join('kategori','kategori.id','=','barang.id_kategori')
            ->select('barang_keluar.id_barang',
                     'barang.id_kategori',
                     'kategori.nama_kategori',
                      DB::raw('sum(jml_bk) as total'))
             ->where(DB::raw("date_format(tgl_keluar,'%Y-%m')"), '=', $bulanx)
             ->groupBy('id_barang','id_kategori','nama_kategori')
             ->orderBy('tgl_keluar')
             ->orderBy('total')
             ->get();
                    
$barang = $kategoriy->groupBy('id_kategori');
$array = collect($barang)->sort($barang->sum('total'))->reverse()->toArray();

and the results I get:

array:5 [▼
  1 => array:1 [▼
    0 => {#939 ▼
      +"id_barang": 1
      +"id_kategori": 1
      +"nama_kategori": "Sayur 1"
      +"total": "400"
    }
  0 => {#939 ▼
      +"id_barang": 2
      +"id_kategori": 1
      +"nama_kategori": "Sayur 2"
      +"total": "200"
    }
  ]
  2 => array:1 [▼
    0 => {#943 ▼
      +"id_barang": 3
      +"id_kategori": 2
      +"nama_kategori": "Daging"
      +"total": "500"
    }
  ]
  3 => array:1 [▼
    0 => {#945 ▼
      +"id_barang": 4
      +"id_kategori": 3
      +"nama_kategori": "Apple"
      +"total": "100"
    }
    0 => {#945 ▼
      +"id_barang": 5
      +"id_kategori": 3
      +"nama_kategori": "Banana"
      +"total": "100"
    }
  ]  
]

How to get the result of sort By('subtotal') from groupBy('category_id'). Please help

I've tried adding query array and sort like this. but the result is still the same as above

$array = collect($barang)->sort($barang->sum('total'))->reverse()->toArray();

Maybe the blade table example that I made would be like this, but sorted by subtotal of the large - small values. Please help

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3qnPioU
via IFTTT