Mengenal Laravel 12 Query Builder dan Tips Codingan Best Practice

Pernah nulis SQL query di Laravel terus bingung mau pakai yang mana? Raw SQL, Query Builder, atau Eloquent? Ketiganya bisa accomplish hal yang sama, tapi masing-masing punya use case yang berbeda.

Di artikel ini, kita akan deep dive ke Query Builder — salah satu fitur Laravel yang powerful tapi sering underutilized atau malah disalahgunakan.

Definisi Query Builder

Query Builder adalah abstraction layer untuk berinteraksi dengan database. Daripada nulis raw SQL string, kamu bisa pakai fluent interface dengan method chaining yang lebih readable dan secure.

// Raw SQL (risky, verbose)
$users = DB::select("SELECT * FROM users WHERE status = 'active' AND role = 'admin'");

// Query Builder (safe, readable)
$users = DB::table('users')
    ->where('status', 'active')
    ->where('role', 'admin')
    ->get();

Keduanya menghasilkan hasil yang sama, tapi Query Builder punya beberapa keunggulan:

1. Database Agnostic — Kode yang sama bisa jalan di MySQL, PostgreSQL, SQLite, dan SQL Server
2. Built-in Security — Parameter binding otomatis, aman dari SQL injection
3. Fluent Interface — Method chaining yang readable
4. IDE Support — Autocomplete dan type hints

Posisi Query Builder dalam Laravel

┌─────────────────────────────────────────────────┐
│                 ELOQUENT ORM                    │
│    (Model, Relationships, Mutators, Events)    │
│         "Bekerja dengan objects/models"        │
├─────────────────────────────────────────────────┤
│              QUERY BUILDER                      │
│      (Fluent interface, Method chaining)       │
│         "Bekerja dengan data mentah"           │
├─────────────────────────────────────────────────┤
│              PDO / RAW SQL                      │
│         (Direct database access)               │
│      "Full control, full responsibility"       │
└─────────────────────────────────────────────────┘

Eloquent sebenarnya built on top of Query Builder. Ketika kamu panggil User::where('status', 'active')->get(), di balik layar Eloquent menggunakan Query Builder.

Kapan Pakai Apa?

DECISION GUIDE:

Raw SQL:
├── Database-specific features (MySQL FULLTEXT, PostgreSQL JSONB operators)
├── Extremely complex queries yang susah di-express dengan Query Builder
├── Legacy queries yang sudah proven works
└── Performance-critical queries yang perlu di-tune manual

Query Builder:
├── Aggregations dan reports (COUNT, SUM, AVG)
├── Complex JOINs tanpa need for model relationships
├── Bulk operations (mass insert, update, delete)
├── Queries ke tables yang tidak punya Model
└── Performance-sensitive operations

Eloquent:
├── Standard CRUD operations
├── Working with relationships (hasMany, belongsTo, etc.)
├── Need model events (creating, updated, deleted)
├── Need mutators dan accessors
├── API Resources dan serialization
└── Code readability adalah priority

Apa yang Akan Kamu Pelajari

Di artikel ini, kita akan cover:

1. CRUD Operations — Select, Insert, Update, Delete dengan Query Builder
2. WHERE Clauses — Semua variasi filtering data
3. JOINs & Subqueries — Menggabungkan data dari multiple tables
4. Aggregates & Grouping — COUNT, SUM, GROUP BY, HAVING
5. Anti-Patterns — Kesalahan umum dan cara fix-nya
6. Performance Tips — Optimasi query untuk production
7. Real-World Case Studies — Contoh implementasi nyata

Mari kita mulai dari basics.

---

Bagian 2: Query Builder Basics — CRUD Operations

Setup & Connection

Query Builder diakses melalui DB facade. Secara default, Laravel akan menggunakan connection yang di-set di .env file.

use Illuminate\\Support\\Facades\\DB;

// Default connection
$users = DB::table('users')->get();

// Specific connection (jika punya multiple databases)
$users = DB::connection('mysql_read')->table('users')->get();

SELECT Operations

get() — Mengambil Semua Records

// Ambil semua users
$users = DB::table('users')->get();

// Returns: Illuminate\\Support\\Collection
foreach ($users as $user) {
    echo $user->name;
}

get() mengembalikan Collection, bukan array. Ini artinya kamu bisa langsung pakai Collection methods seperti map(), filter(), pluck(), dll.

first() — Mengambil Satu Record

// Ambil user pertama yang match
$user = DB::table('users')
    ->where('email', '[email protected]')
    ->first();

if ($user) {
    echo $user->name;
}

// firstOrFail() — Throw exception jika tidak ketemu
$user = DB::table('users')
    ->where('email', '[email protected]')
    ->firstOrFail();

find() — Mencari berdasarkan Primary Key

// Cari user dengan id = 1
$user = DB::table('users')->find(1);

// Cari multiple users
$users = DB::table('users')->find([1, 2, 3]);

value() — Mengambil Single Value

Ketika kamu hanya butuh satu column dari satu record:

// Ambil email dari user dengan id = 1
$email = DB::table('users')
    ->where('id', 1)
    ->value('email');

// Returns: "[email protected]" (string, bukan object)

pluck() — Mengambil Column sebagai Array/Collection

// Ambil semua email sebagai collection
$emails = DB::table('users')->pluck('email');
// Returns: ["[email protected]", "[email protected]", ...]

// Pluck dengan key
$users = DB::table('users')->pluck('name', 'id');
// Returns: [1 => "John", 2 => "Jane", ...]
// Useful untuk dropdown options!

select() — Specify Columns

Best Practice: Jangan pakai SELECT *. Specify columns yang kamu butuhkan.

// ❌ Bad - mengambil semua columns
$users = DB::table('users')->get();

// ✅ Good - hanya columns yang dibutuhkan
$users = DB::table('users')
    ->select('id', 'name', 'email')
    ->get();

// Dengan alias
$users = DB::table('users')
    ->select('id', 'name', 'email as user_email')
    ->get();

// Tambah column ke existing select
$query = DB::table('users')->select('id', 'name');
$users = $query->addSelect('email')->get();

distinct() — Unique Values

// Ambil unique categories
$categories = DB::table('products')
    ->select('category')
    ->distinct()
    ->get();

---

INSERT Operations

insert() — Insert Single atau Multiple Records

// Insert single record
DB::table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => bcrypt('password'),
    'created_at' => now(),
    'updated_at' => now(),
]);

// Insert multiple records (batch insert)
DB::table('users')->insert([
    [
        'name' => 'John Doe',
        'email' => '[email protected]',
        'password' => bcrypt('password'),
        'created_at' => now(),
        'updated_at' => now(),
    ],
    [
        'name' => 'Jane Doe',
        'email' => '[email protected]',
        'password' => bcrypt('password'),
        'created_at' => now(),
        'updated_at' => now(),
    ],
]);

Note: insert() tidak auto-fill created_at dan updated_at. Kamu harus manual set atau pakai Eloquent.

insertOrIgnore() — Ignore Duplicates

// Insert, tapi ignore jika duplicate key
DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => '[email protected]'],
    ['id' => 2, 'email' => '[email protected]'],
]);

insertGetId() — Insert dan Return ID

// Insert dan dapat ID yang baru di-create
$id = DB::table('users')->insertGetId([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => bcrypt('password'),
]);

echo $id; // 123

upsert() — Insert or Update (Laravel 8+)

Ini sangat berguna untuk sync data:

DB::table('users')->upsert(
    [
        // Data yang mau di-insert/update
        ['email' => '[email protected]', 'name' => 'John Updated', 'votes' => 1],
        ['email' => '[email protected]', 'name' => 'Jane Updated', 'votes' => 2],
    ],
    ['email'],           // Unique key untuk check existing
    ['name', 'votes']    // Columns yang di-update jika exists
);

Logic:

• Jika email belum ada → INSERT
• Jika email sudah ada → UPDATE name dan votes

---

UPDATE Operations

update() — Update Records

// Update single field
DB::table('users')
    ->where('id', 1)
    ->update(['name' => 'John Updated']);

// Update multiple fields
DB::table('users')
    ->where('id', 1)
    ->update([
        'name' => 'John Updated',
        'email' => '[email protected]',
        'updated_at' => now(),
    ]);

// Update dengan condition
DB::table('users')
    ->where('status', 'inactive')
    ->where('last_login', '<', now()->subMonths(6))
    ->update(['status' => 'archived']);

updateOrInsert() — Upsert Single Record

DB::table('users')->updateOrInsert(
    ['email' => '[email protected]'],  // Search criteria
    ['name' => 'John Doe', 'votes' => 1]  // Values to set
);

increment() / decrement() — Tambah atau Kurang Value

// Tambah votes by 1
DB::table('users')
    ->where('id', 1)
    ->increment('votes');

// Tambah votes by 5
DB::table('users')
    ->where('id', 1)
    ->increment('votes', 5);

// Increment dengan update field lain
DB::table('users')
    ->where('id', 1)
    ->increment('votes', 1, ['last_voted_at' => now()]);

// Decrement
DB::table('products')
    ->where('id', 1)
    ->decrement('stock', 3);

---

DELETE Operations

delete() — Hapus Records

// Delete dengan condition
DB::table('users')
    ->where('status', 'inactive')
    ->delete();

// Delete by ID
DB::table('users')->where('id', 1)->delete();

// Delete semua records di table (hati-hati!)
DB::table('logs')->delete();

truncate() — Kosongkan Table

// Hapus semua data dan reset auto-increment
DB::table('logs')->truncate();

Warning: truncate() tidak bisa di-rollback dan tidak trigger model events.

---

CRUD Summary

QUICK REFERENCE:

READ:
├── get()          → Collection of all matching records
├── first()        → Single record (or null)
├── find($id)      → Record by primary key
├── value('col')   → Single column value
├── pluck('col')   → Collection of column values
└── select()       → Specify columns to retrieve

CREATE:
├── insert([])     → Insert record(s), returns bool
├── insertGetId()  → Insert and return ID
├── insertOrIgnore → Insert, ignore duplicates
└── upsert()       → Insert or update (batch)

UPDATE:
├── update([])     → Update matching records
├── updateOrInsert → Update or insert single record
├── increment()    → Increase numeric column
└── decrement()    → Decrease numeric column

DELETE:
├── delete()       → Delete matching records
└── truncate()     → Empty entire table

Di bagian selanjutnya, kita akan explore WHERE clauses yang lebih advanced untuk filtering data dengan berbagai kondisi.

Bagian 3: WHERE Clauses — Filtering Data

WHERE clause adalah jantung dari query. Di bagian ini, kita akan explore semua variasi WHERE yang tersedia di Query Builder.

Basic Where

Simple Where

// Equality (default operator)
$users = DB::table('users')
    ->where('status', 'active')
    ->get();
// SQL: SELECT * FROM users WHERE status = 'active'

// Dengan operator explicit
$users = DB::table('users')
    ->where('status', '=', 'active')
    ->get();

// Comparison operators
$products = DB::table('products')
    ->where('price', '>', 100000)
    ->get();

$products = DB::table('products')
    ->where('price', '>=', 100000)
    ->get();

$products = DB::table('products')
    ->where('stock', '<', 10)
    ->get();

$products = DB::table('products')
    ->where('category', '!=', 'electronics')
    ->get();

// LIKE operator
$users = DB::table('users')
    ->where('name', 'like', 'John%')
    ->get();
// Nama yang dimulai dengan "John"

$users = DB::table('users')
    ->where('email', 'like', '%@gmail.com')
    ->get();
// Email yang berakhiran @gmail.com

Multiple Conditions (AND)

// Chained where = AND
$users = DB::table('users')
    ->where('status', 'active')
    ->where('role', 'admin')
    ->where('email_verified_at', '!=', null)
    ->get();
// SQL: WHERE status = 'active' AND role = 'admin' AND email_verified_at IS NOT NULL

// Array syntax (cleaner untuk multiple conditions)
$users = DB::table('users')
    ->where([
        ['status', '=', 'active'],
        ['role', '=', 'admin'],
    ])
    ->get();

OR Conditions

// orWhere
$users = DB::table('users')
    ->where('role', 'admin')
    ->orWhere('role', 'moderator')
    ->get();
// SQL: WHERE role = 'admin' OR role = 'moderator'

⚠️ Hati-hati dengan orWhere!

// ❌ Problematic - Logic mungkin tidak sesuai ekspektasi
$users = DB::table('users')
    ->where('status', 'active')
    ->where('role', 'admin')
    ->orWhere('role', 'moderator')
    ->get();
// SQL: WHERE status = 'active' AND role = 'admin' OR role = 'moderator'
// Ini akan return: (active admin) ATAU (any moderator)

// ✅ Correct - Gunakan closure untuk grouping
$users = DB::table('users')
    ->where('status', 'active')
    ->where(function ($query) {
        $query->where('role', 'admin')
              ->orWhere('role', 'moderator');
    })
    ->get();
// SQL: WHERE status = 'active' AND (role = 'admin' OR role = 'moderator')
// Ini akan return: active users yang admin ATAU moderator

---

Advanced Where Clauses

whereIn() / whereNotIn()

// WHERE IN
$users = DB::table('users')
    ->whereIn('id', [1, 2, 3, 4, 5])
    ->get();
// SQL: WHERE id IN (1, 2, 3, 4, 5)

$orders = DB::table('orders')
    ->whereIn('status', ['pending', 'processing'])
    ->get();

// WHERE NOT IN
$users = DB::table('users')
    ->whereNotIn('role', ['banned', 'suspended'])
    ->get();

// Subquery dalam whereIn
$activeOrderUserIds = DB::table('orders')
    ->select('user_id')
    ->where('created_at', '>', now()->subDays(30));

$users = DB::table('users')
    ->whereIn('id', $activeOrderUserIds)
    ->get();
// Users yang punya order dalam 30 hari terakhir

whereBetween() / whereNotBetween()

// Numeric range
$products = DB::table('products')
    ->whereBetween('price', [100000, 500000])
    ->get();
// SQL: WHERE price BETWEEN 100000 AND 500000

// Date range
$orders = DB::table('orders')
    ->whereBetween('created_at', ['2024-01-01', '2024-12-31'])
    ->get();

// Dengan Carbon
$orders = DB::table('orders')
    ->whereBetween('created_at', [
        now()->startOfMonth(),
        now()->endOfMonth()
    ])
    ->get();
// Orders bulan ini

// NOT BETWEEN
$products = DB::table('products')
    ->whereNotBetween('price', [100000, 500000])
    ->get();

whereNull() / whereNotNull()

// NULL check
$unverifiedUsers = DB::table('users')
    ->whereNull('email_verified_at')
    ->get();

$verifiedUsers = DB::table('users')
    ->whereNotNull('email_verified_at')
    ->get();

// Multiple null checks
$incompleteProfiles = DB::table('users')
    ->whereNull('phone')
    ->orWhereNull('address')
    ->get();

Date/Time Where Clauses

Laravel menyediakan helper methods untuk query berdasarkan date/time:

// whereDate - Match exact date
$orders = DB::table('orders')
    ->whereDate('created_at', '2024-06-15')
    ->get();

// whereMonth - Match month (1-12)
$orders = DB::table('orders')
    ->whereMonth('created_at', 12)
    ->get();
// Orders di bulan Desember

// whereYear - Match year
$orders = DB::table('orders')
    ->whereYear('created_at', 2024)
    ->get();

// whereDay - Match day of month (1-31)
$orders = DB::table('orders')
    ->whereDay('created_at', 1)
    ->get();
// Orders di tanggal 1

// whereTime - Match time
$logs = DB::table('activity_logs')
    ->whereTime('created_at', '>', '09:00:00')
    ->whereTime('created_at', '<', '17:00:00')
    ->get();
// Activity antara jam 9 pagi - 5 sore

// Kombinasi
$orders = DB::table('orders')
    ->whereYear('created_at', 2024)
    ->whereMonth('created_at', 6)
    ->get();
// Orders di Juni 2024

whereColumn() — Comparing Columns

// Products dimana sale_price lebih kecil dari regular_price
$discountedProducts = DB::table('products')
    ->whereColumn('sale_price', '<', 'regular_price')
    ->get();

// Users dimana created_at sama dengan updated_at (never updated)
$neverUpdated = DB::table('users')
    ->whereColumn('created_at', 'updated_at')
    ->get();

// Multiple column comparisons
$validRecords = DB::table('records')
    ->whereColumn([
        ['start_date', '<=', 'end_date'],
        ['created_by', '=', 'updated_by'],
    ])
    ->get();

---

Where dengan Closure (Grouping)

Closure sangat penting untuk complex logic dengan AND/OR combinations:

// Complex condition:
// Active users yang (admin ATAU moderator) DAN (verified ATAU created dalam 7 hari)
$users = DB::table('users')
    ->where('status', 'active')
    ->where(function ($query) {
        $query->where('role', 'admin')
              ->orWhere('role', 'moderator');
    })
    ->where(function ($query) {
        $query->whereNotNull('email_verified_at')
              ->orWhere('created_at', '>', now()->subDays(7));
    })
    ->get();

// SQL:
// SELECT * FROM users
// WHERE status = 'active'
// AND (role = 'admin' OR role = 'moderator')
// AND (email_verified_at IS NOT NULL OR created_at > '2024-06-08')

---

JSON Where Clauses

Untuk columns dengan JSON data type:

// Assuming 'preferences' column stores JSON: {"theme": "dark", "language": "id"}

// Simple JSON where
$users = DB::table('users')
    ->where('preferences->theme', 'dark')
    ->get();

// Nested JSON
// {"settings": {"notifications": {"email": true}}}
$users = DB::table('users')
    ->where('settings->notifications->email', true)
    ->get();

// whereJsonContains - Check if array contains value
// {"tags": ["php", "laravel", "vue"]}
$posts = DB::table('posts')
    ->whereJsonContains('tags', 'laravel')
    ->get();

// Multiple values
$posts = DB::table('posts')
    ->whereJsonContains('tags', ['php', 'laravel'])
    ->get();

// whereJsonLength - Check array length
$posts = DB::table('posts')
    ->whereJsonLength('tags', '>', 3)
    ->get();
// Posts dengan lebih dari 3 tags

---

Exists Queries

// whereExists - Users yang punya minimal 1 order
$users = DB::table('users')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
              ->from('orders')
              ->whereColumn('orders.user_id', 'users.id');
    })
    ->get();

// SQL:
// SELECT * FROM users
// WHERE EXISTS (
//     SELECT 1 FROM orders WHERE orders.user_id = users.id
// )

// whereNotExists - Users yang belum pernah order
$users = DB::table('users')
    ->whereNotExists(function ($query) {
        $query->select(DB::raw(1))
              ->from('orders')
              ->whereColumn('orders.user_id', 'users.id');
    })
    ->get();

---

Raw Where Expressions

Untuk cases yang tidak bisa di-cover oleh built-in methods:

// whereRaw
$users = DB::table('users')
    ->whereRaw('YEAR(created_at) = YEAR(CURDATE())')
    ->get();
// Users yang dibuat tahun ini (MySQL specific)

// Dengan bindings (ALWAYS use bindings untuk user input!)
$users = DB::table('users')
    ->whereRaw('LOWER(name) = ?', [strtolower($searchName)])
    ->get();

// orWhereRaw
$products = DB::table('products')
    ->where('category', 'electronics')
    ->orWhereRaw('price * quantity > ?', [1000000])
    ->get();

⚠️ Security Warning: Selalu gunakan parameter bindings (?) untuk user input di raw queries!

---

Bagian 4: JOINs dan Subqueries

Ketika data tersebar di multiple tables, JOIN adalah cara untuk menggabungkannya.

Types of Joins

INNER JOIN

Hanya menampilkan records yang match di kedua tables.

$orders = DB::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->select('orders.*', 'users.name as customer_name', 'users.email')
    ->get();

// SQL:
// SELECT orders.*, users.name as customer_name, users.email
// FROM orders
// INNER JOIN users ON orders.user_id = users.id

LEFT JOIN

Semua records dari table kiri, plus matching records dari table kanan (NULL jika tidak match).

// Semua users dengan order count (termasuk yang belum pernah order)
$users = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', DB::raw('COUNT(orders.id) as order_count'))
    ->groupBy('users.id')
    ->get();

// Users yang belum pernah order
$usersWithoutOrders = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->whereNull('orders.id')
    ->select('users.*')
    ->get();

RIGHT JOIN

Kebalikan dari LEFT JOIN — semua records dari table kanan.

$data = DB::table('orders')
    ->rightJoin('users', 'orders.user_id', '=', 'users.id')
    ->select('users.name', 'orders.id as order_id')
    ->get();

CROSS JOIN

Cartesian product — setiap row dari table pertama dipasangkan dengan setiap row dari table kedua.

// Kombinasi semua sizes dengan semua colors
$combinations = DB::table('sizes')
    ->crossJoin('colors')
    ->select('sizes.name as size', 'colors.name as color')
    ->get();

---

Advanced Join dengan Closure

Untuk join conditions yang lebih complex:

// Join dengan multiple conditions
$orders = DB::table('orders')
    ->join('users', function ($join) {
        $join->on('orders.user_id', '=', 'users.id')
             ->where('users.status', '=', 'active');
    })
    ->get();

// Join dengan OR condition
$data = DB::table('posts')
    ->join('comments', function ($join) {
        $join->on('posts.id', '=', 'comments.post_id')
             ->orOn('posts.id', '=', 'comments.parent_post_id');
    })
    ->get();

---

Multiple Joins

// Orders dengan user dan product info
$orders = DB::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->join('order_items', 'orders.id', '=', 'order_items.order_id')
    ->join('products', 'order_items.product_id', '=', 'products.id')
    ->select(
        'orders.id as order_id',
        'orders.created_at',
        'users.name as customer',
        'products.name as product',
        'order_items.quantity',
        'order_items.price'
    )
    ->get();

---

Subqueries

Subquery di SELECT

// Users dengan total order amount
$users = DB::table('users')
    ->select('users.*')
    ->selectSub(function ($query) {
        $query->from('orders')
              ->selectRaw('COALESCE(SUM(total), 0)')
              ->whereColumn('orders.user_id', 'users.id');
    }, 'total_spent')
    ->get();

// SQL:
// SELECT users.*, (
//     SELECT COALESCE(SUM(total), 0) FROM orders
//     WHERE orders.user_id = users.id
// ) as total_spent
// FROM users

Subquery di FROM (Derived Table)

// Latest order per user
$latestOrders = DB::table('orders')
    ->select('user_id', DB::raw('MAX(created_at) as last_order_at'))
    ->groupBy('user_id');

$users = DB::table('users')
    ->joinSub($latestOrders, 'latest_orders', function ($join) {
        $join->on('users.id', '=', 'latest_orders.user_id');
    })
    ->select('users.*', 'latest_orders.last_order_at')
    ->get();

Subquery di WHERE

// Users yang order amount-nya di atas rata-rata
$avgAmount = DB::table('orders')->avg('total');

$users = DB::table('users')
    ->whereIn('id', function ($query) use ($avgAmount) {
        $query->select('user_id')
              ->from('orders')
              ->groupBy('user_id')
              ->havingRaw('SUM(total) > ?', [$avgAmount]);
    })
    ->get();

---

Union Queries

Menggabungkan hasil dari multiple queries:

// Staff = admins + moderators
$admins = DB::table('users')
    ->select('id', 'name', 'email')
    ->where('role', 'admin');

$moderators = DB::table('users')
    ->select('id', 'name', 'email')
    ->where('role', 'moderator');

$staff = $admins->union($moderators)->get();

// unionAll (termasuk duplicates)
$all = $admins->unionAll($moderators)->get();

---

Query Builder vs Eloquent untuk Joins

Kapan pakai JOIN (Query Builder) vs Eloquent Relationships?

PAKAI JOIN (Query Builder) KETIKA:

✓ Complex aggregations (SUM, COUNT, AVG across tables)
✓ Report generation
✓ Data tidak perlu jadi Model objects
✓ Performance-critical queries
✓ One-time queries yang tidak perlu reusable

PAKAI ELOQUENT RELATIONSHIPS KETIKA:

✓ Standard CRUD operations
✓ Perlu access model methods/attributes
✓ Perlu model events (created, updated, etc.)
✓ Query logic akan di-reuse
✓ Code readability adalah priority

Contoh Perbandingan:

// Query Builder - untuk report
$report = DB::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->join('products', 'orders.product_id', '=', 'products.id')
    ->select(
        'users.name',
        DB::raw('COUNT(*) as total_orders'),
        DB::raw('SUM(orders.total) as revenue')
    )
    ->groupBy('users.id', 'users.name')
    ->orderByDesc('revenue')
    ->limit(10)
    ->get();

// Eloquent - untuk standard operations
$user = User::with(['orders' => function ($query) {
    $query->with('product')->latest()->limit(5);
}])->find($userId);

foreach ($user->orders as $order) {
    echo $order->product->name;
}

Keduanya valid, tapi untuk use case yang berbeda. Di bagian selanjutnya, kita akan explore Aggregates dan Grouping untuk reporting.

Bagian 5: Aggregates & Grouping

Aggregates adalah fondasi untuk reporting dan analytics. Di bagian ini, kita akan explore semua aggregate functions dan cara mengelompokkan data.

Aggregate Functions

count() — Menghitung Jumlah Records

// Count semua records
$totalUsers = DB::table('users')->count();

// Count dengan condition
$activeUsers = DB::table('users')
    ->where('status', 'active')
    ->count();

// Count specific column (exclude NULL)
$verifiedCount = DB::table('users')
    ->count('email_verified_at');
// Hanya count yang email_verified_at tidak NULL

max() / min() — Nilai Maksimum dan Minimum

// Harga tertinggi
$maxPrice = DB::table('products')->max('price');

// Harga terendah
$minPrice = DB::table('products')->min('price');

// Dengan condition
$maxOrderTotal = DB::table('orders')
    ->where('status', 'completed')
    ->max('total');

// Min date (earliest)
$firstOrder = DB::table('orders')->min('created_at');

avg() — Rata-rata

// Rata-rata harga
$avgPrice = DB::table('products')->avg('price');

// Rata-rata order value untuk completed orders
$avgOrderValue = DB::table('orders')
    ->where('status', 'completed')
    ->avg('total');

sum() — Total

// Total revenue
$totalRevenue = DB::table('orders')
    ->where('status', 'completed')
    ->sum('total');

// Total stock
$totalStock = DB::table('products')
    ->where('is_active', true)
    ->sum('stock');

Combining Aggregates

// Multiple aggregates dalam satu query
$stats = DB::table('orders')
    ->where('status', 'completed')
    ->selectRaw('
        COUNT(*) as total_orders,
        SUM(total) as revenue,
        AVG(total) as avg_order_value,
        MIN(total) as smallest_order,
        MAX(total) as largest_order
    ')
    ->first();

echo $stats->total_orders;    // 1500
echo $stats->revenue;         // 75000000
echo $stats->avg_order_value; // 50000

---

GROUP BY

Mengelompokkan data untuk aggregate per group.

// Sales per category
$salesByCategory = DB::table('products')
    ->join('order_items', 'products.id', '=', 'order_items.product_id')
    ->select(
        'products.category',
        DB::raw('COUNT(*) as items_sold'),
        DB::raw('SUM(order_items.quantity) as total_quantity'),
        DB::raw('SUM(order_items.quantity * order_items.price) as revenue')
    )
    ->groupBy('products.category')
    ->get();

// Output:
// category     | items_sold | total_quantity | revenue
// -------------|------------|----------------|----------
// Electronics  | 500        | 1200           | 45000000
// Fashion      | 800        | 2500           | 32000000
// Food         | 1200       | 5000           | 15000000

Multiple Column Grouping

// Sales per category per month
$monthlySales = DB::table('orders')
    ->join('order_items', 'orders.id', '=', 'order_items.order_id')
    ->join('products', 'order_items.product_id', '=', 'products.id')
    ->select(
        'products.category',
        DB::raw('YEAR(orders.created_at) as year'),
        DB::raw('MONTH(orders.created_at) as month'),
        DB::raw('SUM(order_items.quantity * order_items.price) as revenue')
    )
    ->where('orders.status', 'completed')
    ->groupBy('products.category', DB::raw('YEAR(orders.created_at)'), DB::raw('MONTH(orders.created_at)'))
    ->orderBy('year')
    ->orderBy('month')
    ->get();

---

HAVING — Filter Setelah GROUP BY

WHERE filter sebelum grouping, HAVING filter setelah grouping.

// Categories dengan lebih dari 100 produk
$popularCategories = DB::table('products')
    ->select('category', DB::raw('COUNT(*) as product_count'))
    ->groupBy('category')
    ->having('product_count', '>', 100)
    ->get();

// Customers yang sudah belanja lebih dari 10 juta
$vipCustomers = DB::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->select('users.id', 'users.name', DB::raw('SUM(orders.total) as total_spent'))
    ->where('orders.status', 'completed')
    ->groupBy('users.id', 'users.name')
    ->having('total_spent', '>', 10000000)
    ->orderByDesc('total_spent')
    ->get();

// havingRaw untuk complex conditions
$data = DB::table('orders')
    ->select('user_id', DB::raw('COUNT(*) as order_count'))
    ->groupBy('user_id')
    ->havingRaw('COUNT(*) > ? AND SUM(total) > ?', [5, 1000000])
    ->get();

---

Ordering & Limiting

orderBy() — Sorting Results

// Single column sort
$users = DB::table('users')
    ->orderBy('name', 'asc')
    ->get();

$users = DB::table('users')
    ->orderBy('created_at', 'desc')
    ->get();

// orderByDesc shorthand
$users = DB::table('users')
    ->orderByDesc('created_at')
    ->get();

// Multiple column sort
$products = DB::table('products')
    ->orderBy('category', 'asc')
    ->orderBy('price', 'desc')
    ->get();
// Sort by category A-Z, then by price highest first

// orderByRaw untuk custom logic
$products = DB::table('products')
    ->orderByRaw('CASE WHEN stock > 0 THEN 0 ELSE 1 END')
    ->orderBy('price', 'asc')
    ->get();
// In-stock products first, then by price

latest() / oldest() — Sort by Timestamp

// Sort by created_at DESC (newest first)
$posts = DB::table('posts')->latest()->get();

// Sort by created_at ASC (oldest first)
$posts = DB::table('posts')->oldest()->get();

// Custom column
$orders = DB::table('orders')->latest('completed_at')->get();

inRandomOrder() — Random Sorting

// Random product
$randomProduct = DB::table('products')
    ->inRandomOrder()
    ->first();

// 5 random featured products
$featuredProducts = DB::table('products')
    ->where('is_featured', true)
    ->inRandomOrder()
    ->limit(5)
    ->get();

reorder() — Reset Ordering

$query = DB::table('users')->orderBy('name');

// Remove existing order and add new one
$users = $query->reorder('created_at', 'desc')->get();

---

Limiting Results

limit() / take() — Batasi Jumlah

// Ambil 10 records pertama
$users = DB::table('users')
    ->limit(10)
    ->get();

// take() adalah alias
$users = DB::table('users')
    ->take(10)
    ->get();

offset() / skip() — Skip Records

// Skip 20, ambil 10 (records 21-30)
$users = DB::table('users')
    ->orderBy('id')
    ->offset(20)
    ->limit(10)
    ->get();

// skip() adalah alias
$users = DB::table('users')
    ->orderBy('id')
    ->skip(20)
    ->take(10)
    ->get();

---

Pagination

Manual offset/limit untuk pagination itu risky. Gunakan built-in pagination.

paginate() — Standard Pagination

// 15 items per page
$users = DB::table('users')
    ->orderBy('id')
    ->paginate(15);

// Di Blade:
// @foreach ($users as $user)
//     {{ $user->name }}
// @endforeach
// {{ $users->links() }}

// Customize page name
$users = DB::table('users')->paginate(15, ['*'], 'user_page');

simplePaginate() — Simpler Pagination

Tidak menghitung total records (faster untuk large datasets).

$users = DB::table('users')
    ->orderBy('id')
    ->simplePaginate(15);

// Hanya ada Previous/Next, tidak ada page numbers

cursorPaginate() — Cursor-based Pagination

Best untuk large datasets dan infinite scroll.

$users = DB::table('users')
    ->orderBy('id')
    ->cursorPaginate(15);

// Benefits:
// - Consistent results even if data changes
// - Better performance untuk deep pagination
// - Ideal untuk infinite scroll

Kapan pakai apa:

paginate():
├── Perlu show page numbers
├── Dataset tidak terlalu besar (< 100k records)
└── User perlu jump ke specific page

simplePaginate():
├── Cukup Previous/Next navigation
├── Lebih cepat dari paginate()
└── Good untuk medium datasets

cursorPaginate():
├── Very large datasets
├── Infinite scroll UI
├── Real-time data (sering insert/delete)
└── Consistency lebih penting dari jumping

---

Bagian 6: Anti-Patterns dan Common Mistakes

Sekarang kita masuk ke bagian penting: kesalahan yang sering terjadi dan cara menghindarinya. Ini berdasarkan ratusan code review yang saya lakukan.

Anti-Pattern #1: SELECT * Everywhere

Problem: Mengambil semua columns padahal hanya butuh beberapa.

// ❌ BAD - Ambil semua 50 columns
$users = DB::table('users')->get();

foreach ($users as $user) {
    echo $user->name; // Cuma pakai name!
}

Kenapa Ini Masalah:

• Memory waste — columns yang tidak dipakai tetap di-load
• Slower query — transfer data lebih banyak
• Security risk — mungkin expose sensitive data

// ✅ GOOD - Select hanya yang dibutuhkan
$users = DB::table('users')
    ->select('id', 'name')
    ->get();

Rule of Thumb: Jika kamu pakai kurang dari 50% columns, selalu explicit select.

---

Anti-Pattern #2: N+1 Query Problem

Problem: Execute query di dalam loop.

// ❌ BAD - N+1 queries
$orders = DB::table('orders')->get();

foreach ($orders as $order) {
    // Query setiap iteration!
    $user = DB::table('users')
        ->where('id', $order->user_id)
        ->first();

    echo $order->id . ' - ' . $user->name;
}

// 100 orders = 101 queries!

Cara Detect: Gunakan Laravel Debugbar atau Telescope. Jika ada 50+ queries untuk satu page load, kemungkinan N+1.

// ✅ GOOD - Single query dengan JOIN
$orders = DB::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->select('orders.*', 'users.name as user_name')
    ->get();

foreach ($orders as $order) {
    echo $order->id . ' - ' . $order->user_name;
}

// 100 orders = 1 query!

---

Anti-Pattern #3: Query di Dalam Loop

Mirip N+1, tapi untuk updates/inserts.

// ❌ BAD - 100 queries untuk 100 users
$userIds = [1, 2, 3, /* ... 100 ids */];

foreach ($userIds as $userId) {
    DB::table('users')
        ->where('id', $userId)
        ->update(['status' => 'inactive']);
}

// ✅ GOOD - 1 query untuk semua
DB::table('users')
    ->whereIn('id', $userIds)
    ->update(['status' => 'inactive']);

Untuk Inserts:

// ❌ BAD
foreach ($items as $item) {
    DB::table('products')->insert($item);
}

// ✅ GOOD - Batch insert
DB::table('products')->insert($items);

---

Anti-Pattern #4: Raw Query Tanpa Binding

Problem: SQL Injection vulnerability.

// ❌ EXTREMELY DANGEROUS
$email = $request->input('email'); // User input!

$users = DB::select("SELECT * FROM users WHERE email = '$email'");

// Attacker bisa input: ' OR '1'='1' --
// Query jadi: SELECT * FROM users WHERE email = '' OR '1'='1' --'
// Return semua users!

// ✅ SAFE - Parameter binding
$users = DB::table('users')
    ->where('email', $email) // Auto-escaped
    ->get();

// Atau jika harus raw:
$users = DB::select(
    "SELECT * FROM users WHERE email = ?",
    [$email]
);

// Named binding
$users = DB::select(
    "SELECT * FROM users WHERE email = :email",
    ['email' => $email]
);

Rule: NEVER concatenate user input ke SQL string. ALWAYS use parameter binding.

---

Anti-Pattern #5: Business Logic di Query

Problem: Logic yang scattered dan hard to test.

// ❌ BAD - Discount logic di query
$products = DB::table('products')
    ->selectRaw('
        *,
        CASE
            WHEN category = "electronics" THEN price * 0.9
            WHEN category = "fashion" THEN price * 0.85
            ELSE price * 0.95
        END as discounted_price
    ')
    ->get();

Problems:

• Logic scattered di query
• Hard to unit test
• Hard to change (perlu modify query)
• Not reusable

// ✅ GOOD - Separate concerns
class DiscountCalculator
{
    public function calculate(string $category, float $price): float
    {
        return match($category) {
            'electronics' => $price * 0.9,
            'fashion' => $price * 0.85,
            default => $price * 0.95,
        };
    }
}

// Query hanya ambil data
$products = DB::table('products')
    ->select('id', 'name', 'category', 'price')
    ->get();

// Process di PHP
$calculator = new DiscountCalculator();
$products = $products->map(function ($product) use ($calculator) {
    $product->discounted_price = $calculator->calculate(
        $product->category,
        $product->price
    );
    return $product;
});

---

Anti-Pattern #6: Tidak Pakai Transactions

Problem: Data inconsistency ketika ada error di tengah operasi.

// ❌ BAD - No transaction
public function createOrder(array $data)
{
    // Step 1: Create order
    $orderId = DB::table('orders')->insertGetId([
        'user_id' => $data['user_id'],
        'total' => $data['total'],
    ]);

    // Step 2: Create order items
    foreach ($data['items'] as $item) {
        DB::table('order_items')->insert([
            'order_id' => $orderId,
            'product_id' => $item['product_id'],
            'quantity' => $item['quantity'],
        ]);
    }

    // Step 3: Reduce stock - ERROR HAPPENS HERE!
    foreach ($data['items'] as $item) {
        DB::table('products')
            ->where('id', $item['product_id'])
            ->decrement('stock', $item['quantity']);
    }

    // Jika error di step 3:
    // - Order sudah di-create ✓
    // - Order items sudah di-create ✓
    // - Stock tidak berkurang ✗
    // = DATA INCONSISTENT!
}

// ✅ GOOD - Wrapped in transaction
public function createOrder(array $data)
{
    return DB::transaction(function () use ($data) {
        $orderId = DB::table('orders')->insertGetId([
            'user_id' => $data['user_id'],
            'total' => $data['total'],
        ]);

        foreach ($data['items'] as $item) {
            DB::table('order_items')->insert([
                'order_id' => $orderId,
                'product_id' => $item['product_id'],
                'quantity' => $item['quantity'],
            ]);

            DB::table('products')
                ->where('id', $item['product_id'])
                ->decrement('stock', $item['quantity']);
        }

        return $orderId;
    });

    // Jika error di mana pun:
    // - Semua di-ROLLBACK
    // - Data tetap consistent
}

Manual Transaction Control:

DB::beginTransaction();

try {
    // Multiple operations...
    DB::table('accounts')
        ->where('id', $fromId)
        ->decrement('balance', $amount);

    DB::table('accounts')
        ->where('id', $toId)
        ->increment('balance', $amount);

    DB::commit();
} catch (\\Exception $e) {
    DB::rollBack();
    throw $e;
}

---

Anti-Pattern Summary Checklist

SEBELUM COMMIT, CEK:

□ Tidak ada SELECT * (kecuali memang butuh semua)
□ Tidak ada query di dalam loop
□ Semua user input pakai parameter binding
□ Complex business logic di PHP, bukan di query
□ Related operations wrapped dalam transaction
□ Indexes sudah ada untuk columns di WHERE/JOIN

Di bagian selanjutnya, kita akan bahas Performance Tips lebih detail, termasuk indexing strategy dan caching.

Bagian 7: Performance Tips & Optimization

Query yang lambat bisa destroy user experience. Di bagian ini, kita akan bahas strategi untuk optimize Query Builder performance.

Indexing Strategy

Index adalah kunci utama database performance. Tanpa index, database harus scan seluruh table (full table scan).

Columns yang Harus Di-Index

// Migration untuk add indexes
Schema::table('orders', function (Blueprint $table) {
    // 1. Foreign keys (biasanya auto-indexed, tapi verify)
    $table->index('user_id');

    // 2. Columns di WHERE clause yang sering dipakai
    $table->index('status');
    $table->index('created_at');

    // 3. Composite index untuk query yang combine columns
    $table->index(['status', 'created_at']);
    $table->index(['user_id', 'status']);

    // 4. Unique constraints
    $table->unique('order_number');
});

Schema::table('products', function (Blueprint $table) {
    // Index untuk search
    $table->index('name');
    $table->index('category');

    // Composite untuk filtered listing
    $table->index(['category', 'is_active', 'price']);
});

Cara Cek Query Pakai Index

// EXPLAIN query
$explanation = DB::table('orders')
    ->where('status', 'pending')
    ->where('created_at', '>', now()->subDays(7))
    ->explain()
    ->get();

dd($explanation);

// Atau langsung di MySQL:
// EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-06-01';

Output EXPLAIN yang Bagus:

• type: "ref" atau "range" (bagus), bukan "ALL" (full scan)
• key: Nama index yang dipakai
• rows: Estimasi rows yang di-scan (makin kecil makin baik)

Index Tips

INDEX BEST PRACTICES:

1. Index columns di WHERE, JOIN, ORDER BY
2. Composite index: urutan column matters!
   - Index ['status', 'created_at'] efektif untuk:
     ✓ WHERE status = 'x'
     ✓ WHERE status = 'x' AND created_at > 'y'
     ✗ WHERE created_at > 'y' (tidak efektif)

3. Jangan over-index
   - Setiap index memperlambat INSERT/UPDATE
   - Index hanya columns yang benar-benar sering di-query

4. Monitor slow queries, tambah index sesuai kebutuhan

---

Chunking untuk Large Datasets

Jangan load semua data sekaligus. Memory akan explode.

chunk() — Process dalam Batches

// Process 1000 records at a time
DB::table('users')
    ->orderBy('id')
    ->chunk(1000, function ($users) {
        foreach ($users as $user) {
            // Process each user
            $this->sendNewsletter($user);
        }
    });

// Return false untuk stop chunking
DB::table('users')
    ->orderBy('id')
    ->chunk(1000, function ($users) {
        foreach ($users as $user) {
            if ($this->shouldStop()) {
                return false; // Stop processing
            }
            // Process user...
        }
    });

chunkById() — Safer for Modifications

Jika kamu modify data selama chunking, pakai chunkById():

// Safe untuk update karena pakai ID-based pagination
DB::table('users')
    ->where('status', 'pending')
    ->chunkById(1000, function ($users) {
        foreach ($users as $user) {
            DB::table('users')
                ->where('id', $user->id)
                ->update(['status' => 'processed']);
        }
    });

lazy() — Lazy Collections

Balance antara memory efficiency dan usability:

// Returns LazyCollection - memory efficient
DB::table('users')
    ->orderBy('id')
    ->lazy()
    ->each(function ($user) {
        // Process one at a time
        $this->processUser($user);
    });

// Bisa pakai Collection methods
$processed = DB::table('users')
    ->orderBy('id')
    ->lazy()
    ->filter(fn ($user) => $user->status === 'active')
    ->map(fn ($user) => $user->email)
    ->all();

cursor() — Most Memory Efficient

Hanya 1 record di memory pada satu waktu:

foreach (DB::table('users')->cursor() as $user) {
    // Process user
    // Hanya 1 user di memory
}

Kapan Pakai Apa:

┌─────────────────┬──────────────┬─────────────┬─────────────────┐
│ Method          │ Memory Usage │ Speed       │ Use Case        │
├─────────────────┼──────────────┼─────────────┼─────────────────┤
│ get()           │ High         │ Fast        │ < 1000 records  │
│ chunk()         │ Medium       │ Medium      │ Batch process   │
│ lazy()          │ Low          │ Medium      │ Transform data  │
│ cursor()        │ Lowest       │ Slower      │ Export/stream   │
└─────────────────┴──────────────┴─────────────┴─────────────────┘

---

Caching Strategies

Cache query results untuk avoid repeated database hits.

Basic Cache

use Illuminate\\Support\\Facades\\Cache;

// Cache for 1 hour (3600 seconds)
$categories = Cache::remember('product_categories', 3600, function () {
    return DB::table('categories')
        ->where('is_active', true)
        ->orderBy('name')
        ->get();
});

// Cache forever (until manually cleared)
$settings = Cache::rememberForever('app_settings', function () {
    return DB::table('settings')->pluck('value', 'key');
});

Cache Tags (Redis/Memcached only)

// Cache dengan tags untuk easy invalidation
$products = Cache::tags(['products', 'catalog'])
    ->remember('featured_products', 3600, function () {
        return DB::table('products')
            ->where('is_featured', true)
            ->get();
    });

// Invalidate semua cache dengan tag 'products'
Cache::tags(['products'])->flush();

// Invalidate specific key
Cache::tags(['products'])->forget('featured_products');

Cache Invalidation Strategy

// Contoh: Invalidate cache ketika data berubah
class ProductService
{
    public function updateProduct(int $id, array $data): void
    {
        DB::table('products')
            ->where('id', $id)
            ->update($data);

        // Clear related caches
        Cache::forget('product_' . $id);
        Cache::tags(['products'])->flush();
    }

    public function getProduct(int $id)
    {
        return Cache::remember('product_' . $id, 3600, function () use ($id) {
            return DB::table('products')->find($id);
        });
    }
}

---

Database Connection Optimization

Read/Write Splitting

Untuk high-traffic apps, pisahkan read dan write ke server berbeda:

// config/database.php
'mysql' => [
    'read' => [
        'host' => [
            '192.168.1.1',  // Read replica 1
            '192.168.1.2',  // Read replica 2
        ],
    ],
    'write' => [
        'host' => [
            '192.168.1.3',  // Primary/Master
        ],
    ],
    'sticky' => true, // Use write connection untuk subsequent reads in same request
    'driver' => 'mysql',
    'database' => 'database',
    'username' => 'root',
    'password' => '',
    // ... other config
],

Force Specific Connection

// Force read connection
$users = DB::connection('mysql::read')->table('users')->get();

// Force write connection
$user = DB::connection('mysql::write')->table('users')->find(1);

---

Monitoring & Debugging

DB::listen() — Log All Queries

// Di AppServiceProvider atau middleware
DB::listen(function ($query) {
    Log::debug('Query executed', [
        'sql' => $query->sql,
        'bindings' => $query->bindings,
        'time' => $query->time . 'ms',
    ]);
});

Slow Query Logging

// Log queries yang lebih dari 100ms
DB::listen(function ($query) {
    if ($query->time > 100) {
        Log::warning('Slow query detected', [
            'sql' => $query->sql,
            'bindings' => $query->bindings,
            'time' => $query->time . 'ms',
            'url' => request()->fullUrl(),
        ]);
    }
});

Laravel Debugbar

Install untuk development:

composer require barryvdh/laravel-debugbar --dev

Debugbar akan show:

• Semua queries yang dijalankan
• Query time
• Duplicate queries (N+1 detection)
• Memory usage

Laravel Telescope

Untuk monitoring lebih comprehensive:

composer require laravel/telescope --dev
php artisan telescope:install
php artisan migrate

---

Bagian 8: Real-World Case Studies & Closing

Mari kita lihat bagaimana Query Builder dipakai di real-world scenarios.

Case Study 1: Dashboard Analytics

Requirement: Dashboard dengan berbagai metrics dan charts.

class DashboardService
{
    public function getStats(Carbon $startDate, Carbon $endDate): array
    {
        // Single query untuk multiple metrics
        $orderStats = DB::table('orders')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->where('status', 'completed')
            ->selectRaw('
                COUNT(*) as total_orders,
                COUNT(DISTINCT user_id) as unique_customers,
                SUM(total) as revenue,
                AVG(total) as avg_order_value
            ')
            ->first();

        return [
            'total_orders' => $orderStats->total_orders,
            'unique_customers' => $orderStats->unique_customers,
            'revenue' => $orderStats->revenue,
            'avg_order_value' => round($orderStats->avg_order_value, 2),
        ];
    }

    public function getDailyRevenue(Carbon $startDate, Carbon $endDate): Collection
    {
        return DB::table('orders')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->where('status', 'completed')
            ->select(
                DB::raw('DATE(created_at) as date'),
                DB::raw('COUNT(*) as orders'),
                DB::raw('SUM(total) as revenue')
            )
            ->groupBy(DB::raw('DATE(created_at)'))
            ->orderBy('date')
            ->get();
    }

    public function getTopProducts(int $limit = 10): Collection
    {
        return DB::table('order_items')
            ->join('products', 'order_items.product_id', '=', 'products.id')
            ->join('orders', 'order_items.order_id', '=', 'orders.id')
            ->where('orders.status', 'completed')
            ->select(
                'products.id',
                'products.name',
                DB::raw('SUM(order_items.quantity) as total_sold'),
                DB::raw('SUM(order_items.quantity * order_items.price) as revenue')
            )
            ->groupBy('products.id', 'products.name')
            ->orderByDesc('revenue')
            ->limit($limit)
            ->get();
    }

    public function getCustomerSegments(): Collection
    {
        return DB::table('orders')
            ->join('users', 'orders.user_id', '=', 'users.id')
            ->where('orders.status', 'completed')
            ->select(
                'users.id',
                'users.name',
                'users.email',
                DB::raw('COUNT(*) as order_count'),
                DB::raw('SUM(orders.total) as total_spent'),
                DB::raw('MAX(orders.created_at) as last_order_at')
            )
            ->groupBy('users.id', 'users.name', 'users.email')
            ->havingRaw('SUM(orders.total) > ?', [1000000])
            ->orderByDesc('total_spent')
            ->get();
    }
}

---

Case Study 2: Advanced Search Feature

Requirement: Product search dengan multiple filters.

class ProductSearchService
{
    public function search(Request $request): LengthAwarePaginator
    {
        $query = DB::table('products')
            ->select(
                'products.id',
                'products.name',
                'products.slug',
                'products.price',
                'products.sale_price',
                'products.stock',
                'products.image',
                'categories.name as category_name',
                'brands.name as brand_name'
            )
            ->leftJoin('categories', 'products.category_id', '=', 'categories.id')
            ->leftJoin('brands', 'products.brand_id', '=', 'brands.id')
            ->where('products.is_active', true);

        // Keyword search
        if ($request->filled('q')) {
            $keyword = $request->q;
            $query->where(function ($q) use ($keyword) {
                $q->where('products.name', 'like', "%{$keyword}%")
                  ->orWhere('products.description', 'like', "%{$keyword}%")
                  ->orWhere('products.sku', 'like', "%{$keyword}%");
            });
        }

        // Category filter
        if ($request->filled('category')) {
            $query->where('categories.slug', $request->category);
        }

        // Brand filter
        if ($request->filled('brand')) {
            $query->where('brands.slug', $request->brand);
        }

        // Price range
        if ($request->filled('min_price')) {
            $query->where('products.price', '>=', $request->min_price);
        }

        if ($request->filled('max_price')) {
            $query->where('products.price', '<=', $request->max_price);
        }

        // In stock only
        if ($request->boolean('in_stock')) {
            $query->where('products.stock', '>', 0);
        }

        // On sale only
        if ($request->boolean('on_sale')) {
            $query->whereNotNull('products.sale_price')
                  ->whereColumn('products.sale_price', '<', 'products.price');
        }

        // Sorting
        $sortField = match($request->input('sort')) {
            'price_low' => 'products.price',
            'price_high' => 'products.price',
            'newest' => 'products.created_at',
            'name' => 'products.name',
            default => 'products.created_at'
        };

        $sortDirection = match($request->input('sort')) {
            'price_low' => 'asc',
            'price_high' => 'desc',
            'name' => 'asc',
            default => 'desc'
        };

        $query->orderBy($sortField, $sortDirection);

        return $query->paginate($request->input('per_page', 20));
    }
}

---

Case Study 3: Data Export dengan Streaming

Requirement: Export large dataset ke CSV tanpa memory overflow.

class ExportService
{
    public function exportOrders(Carbon $startDate, Carbon $endDate): StreamedResponse
    {
        $filename = 'orders_' . $startDate->format('Ymd') . '_' . $endDate->format('Ymd') . '.csv';

        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => "attachment; filename=\\"{$filename}\\"",
        ];

        return response()->stream(function () use ($startDate, $endDate) {
            $handle = fopen('php://output', 'w');

            // Header row
            fputcsv($handle, [
                'Order ID',
                'Order Date',
                'Customer Name',
                'Customer Email',
                'Product',
                'Quantity',
                'Price',
                'Total',
                'Status'
            ]);

            // Stream data dengan cursor (memory efficient!)
            DB::table('orders')
                ->join('users', 'orders.user_id', '=', 'users.id')
                ->join('order_items', 'orders.id', '=', 'order_items.order_id')
                ->join('products', 'order_items.product_id', '=', 'products.id')
                ->whereBetween('orders.created_at', [$startDate, $endDate])
                ->select(
                    'orders.id as order_id',
                    'orders.created_at',
                    'users.name as customer_name',
                    'users.email as customer_email',
                    'products.name as product_name',
                    'order_items.quantity',
                    'order_items.price',
                    'orders.total',
                    'orders.status'
                )
                ->orderBy('orders.id')
                ->cursor()
                ->each(function ($row) use ($handle) {
                    fputcsv($handle, [
                        $row->order_id,
                        $row->created_at,
                        $row->customer_name,
                        $row->customer_email,
                        $row->product_name,
                        $row->quantity,
                        $row->price,
                        $row->total,
                        $row->status
                    ]);
                });

            fclose($handle);
        }, 200, $headers);
    }
}

---

Decision Matrix: Query Builder vs Eloquent

┌────────────────────────────────┬──────────────┬──────────────┐
│ Scenario                       │Query Builder │ Eloquent     │
├────────────────────────────────┼──────────────┼──────────────┤
│ Dashboard aggregations         │ ✓ Prefer     │              │
│ Report generation              │ ✓ Prefer     │              │
│ Complex JOINs untuk analytics  │ ✓ Prefer     │              │
│ Bulk insert/update             │ ✓ Prefer     │              │
│ Data export/streaming          │ ✓ Prefer     │              │
│ Non-model tables (logs, etc)   │ ✓ Required   │              │
├────────────────────────────────┼──────────────┼──────────────┤
│ Standard CRUD operations       │              │ ✓ Prefer     │
│ Working with relationships     │              │ ✓ Prefer     │
│ Model events (observer)        │              │ ✓ Required   │
│ Mutators & Accessors           │              │ ✓ Required   │
│ API Resources/Serialization    │              │ ✓ Prefer     │
│ Form validation with models    │              │ ✓ Prefer     │
└────────────────────────────────┴──────────────┴──────────────┘

---

Best Practices Summary Checklist

QUERY BUILDER BEST PRACTICES:

□ SELECT
  ├── Specify columns yang dibutuhkan, avoid SELECT *
  ├── Use pluck() untuk single column
  ├── Use value() untuk single value
  └── Use first() bukan get()->first()

□ WHERE
  ├── Always use parameter binding (auto di Query Builder)
  ├── Group complex conditions dengan closure
  ├── Use whereIn() untuk multiple values
  └── Use whereBetween() untuk ranges

□ JOINS
  ├── Always specify select columns setelah join
  ├── Use table alias untuk clarity
  ├── Consider Eloquent untuk simple relationships
  └── Use subquery joins untuk complex logic

□ PERFORMANCE
  ├── Add indexes untuk columns di WHERE/JOIN/ORDER BY
  ├── Use chunk()/lazy()/cursor() untuk large datasets
  ├── Cache frequently accessed data
  ├── Monitor slow queries (Debugbar/Telescope)
  └── Use EXPLAIN untuk debug query plans

□ SECURITY
  ├── Never concatenate user input ke raw SQL
  ├── Always use parameter binding untuk raw queries
  ├── Validate input sebelum query
  └── Don't expose detailed errors di production

□ MAINTAINABILITY
  ├── Extract complex queries ke Repository/Service
  ├── Use Query Scopes untuk reusable conditions (Eloquent)
  ├── Add comments untuk complex queries
  └── Keep business logic di PHP, bukan SQL

---

Closing

Laravel Query Builder adalah tool yang powerful untuk interaksi dengan database. Dengan memahami kapan dan bagaimana menggunakannya dengan benar, kamu bisa:

1. Write performant queries — Dengan proper indexing dan optimization
2. Keep code secure — Dengan parameter binding yang automatic
3. Maintain clean codebase — Dengan separation antara Query Builder dan Eloquent sesuai use case
4. Handle large datasets — Dengan chunking dan streaming

Key Takeaways:

• Query Builder untuk data-centric operations: Reports, aggregations, bulk operations
• Eloquent untuk model-centric operations: CRUD, relationships, business logic
• Performance matters: Index properly, avoid N+1, cache when possible
• Security first: Always use parameter binding, validate inputs

Mulai apply best practices ini di project kamu, dan kamu akan lihat perbedaan significant di code quality dan performance.

Happy coding! 🚀

---

Artikel ini ditulis oleh Angga Risky Setiawan, Founder BuildWithAngga. Untuk pembelajaran Laravel lebih lanjut, kunjungi buildwithangga.com