Laravel : SubQuery Join dengan Query Builder

Saya membangun sebuah aplikasi web sederhana untuk mencatat setiap pengeluaran yang saya lakukan (daily expense) menggunakan framework Laravel. Pada aplikasi ini, terdapat sebuah fitur rekap pengeluaran yang di dalamnya berisi tentang daftar total nominal pengeluaran berdasarkan kategori. Data yang ditampilkan berupa nama kategori, total nominal pengeluaran dan besaran prosentasenya.

Tampilan yang ingin saya buat adalah seperti ini

Sebelum kita membahas tentang bagaimana query yang kita perlukan, saya informasikan terlebih dahulu mengenai table apa saja yang saya butuhkan untuk mendapatkan data-data tersebut, serta bagaimana struktur table-nya.

1. Table expenses
   • id
   • category_id
   • date
   • description
   • amount
2. Table categories
   • id
   • name

Berdasarkan kebutuhan seperti pada gambar sebelumnya, kita dapat membuat sebuah MySQL query dengan query-builder dari laravel.

Pada Category model saya membuat sebuah method seperti ini

public static function reportExpenseCategoryDateRange($dateStart, $dateEnd)
{
        $expenseTable = (new Expense())->getTable();
        $categoryTable = (new Category())->getTable();

        $orderedWithCategories = DB::table($expenseTable)
                ->select(DB::raw("
                        {$expenseTable}.category_id,
                        SUM({$expenseTable}.amount) AS amount_sum,
                        {$categoryTable}.name"))
                ->join($categoryTable, "{$expenseTable}.category_id", '=', "{$categoryTable}.id")
                ->whereBetween("{$expenseTable}.date", [$dateStart, $dateEnd])
                ->groupBy(DB::raw("{$expenseTable}.category_id, {$categoryTable}.name"))
                ->orderBy("{$categoryTable}.name", 'asc');

        return $orderedWithCategories->get();
}

Kita harus melakukan join pada kedua table tersebut menggunakan field category_id pada expenses table dan group by category_id karena kita perlu untuk menjumlahkan field amount.

Karena kita menginginkan untuk bisa di-filter per bulan, maka kita dapat memanfaatkan query between date.

Hasil yang akan kita dapat akan seperti ini

Namun, setelah beberapa waktu saya menginginkan data rekap tersebut diurutkan berdasarkan total nominal, bukan berdasarkan nama kategori.

Karena query-builder laravel cukup menantang, maka saya tidak lantas merubah query-builder di model Category yang sebelumnya saya buat, melainkan saya menuliskan raw query terlebih dahulu kemudian "menerjemahkannya" ke dalam bentuk query-builder laravel.

Query yang kita perlukan saat ini terdapat sub-query, kemudian kita join, bentuk raw query mysql nya adalah seperti ini

SELECT c.id, c.name, e.amount_sum
FROM (
	SELECT category_id, SUM(amount) AS amount_sum
	FROM expenses 
	WHERE `date` BETWEEN '2021-11-01' AND '2021-11-31'
	GROUP BY category_id
) e
INNER JOIN categories c ON c.id = e.category_id
ORDER BY e.amount_sum DESC

Lalu bagaimana bentuk query-builder laravel-nya? Seperti yang saya sebutkan sebelumnya, hal ini sangat menantang dan saya perlu mencari referensi terlebih dahulu, termasuk membaca official docs dari laravel tentang Query Builder. Akhirnya saya mendapatkan jawaban tersebut dari official docs laravel yang terdapat pada bagian SubQuery Join.

Jadi, kita dapat mengubah method yang sebelumnya menjadi seperti ini:

public static function reportExpenseCategoryDateRange($dateStart, $dateEnd)
{
        $expenseTable = (new Expense())->getTable();
        $categoryTable = (new Category())->getTable();

        $expenses = DB::table($expenseTable)
                    ->select(DB::raw("{$expenseTable}.category_id, SUM({$expenseTable}.amount) AS amount_sum"))
                    ->whereBetween("{$expenseTable}.date", [$dateStart, $dateEnd])
                    ->groupBy(DB::raw("{$expenseTable}.category_id"));

        $orderedWithCategories = DB::table($categoryTable)
                                ->joinSub($expenses, 'expenses', function ($join) use ($categoryTable) {
                                    $join->on("{$categoryTable}.id", '=', 'expenses.category_id');
                                })
                                ->orderBy("expenses.amount_sum", 'desc');

        return $orderedWithCategories->get();
}

Kita buat query untuk mendapatkan data dari table expenses kemudian kita join dengan table categories sebagai sub-query.

Kita akan mendapatkan hasil sesuai yang kita inginkan seperti ini

Terlihat, saat ini data sudah berurutan berdasarkan total nominal yang terbesar.