Pagination, Filtering, dan API Query Optimization - Perwira Learning Center

1. LATAR BELAKANG

    Setelah mempelajari relasi database dan advanced Eloquent di hari pertama, tantangan berikutnya adalah bagaimana menyajikan data tersebut secara efisien kepada client. API yang mengembalikan ribuan data sekaligus tidak hanya boros bandwidth, tetapi juga memperlambat aplikasi client dan membebani server.

Bayangkan jika endpoint /api/posts mengembalikan 10.000 post sekaligus. Client mobile dengan koneksi lambat akan timeout, browser frontend akan freeze, dan server kita akan kehabisan memory. Di sinilah pentingnya pagination, filtering, dan query optimization.

Pada hari kedua minggu ini, saya mempelajari teknik-teknik untuk:

1. Pagination - Membagi data menjadi halaman-halaman kecil
2. Filtering & Searching - Memungkinkan client meminta data spesifik
3. Sorting - Mengurutkan data sesuai kebutuhan
4. Query Optimization - Memastikan query berjalan cepat bahkan dengan jutaan data
5. Response Format yang Informatif - Memberikan metadata tentang pagination

Dengan teknik-teknik ini, API kita akan menjadi lebih profesional, efisien, dan scalable.

---

2. ALAT DAN BAHAN

2.1 Perangkat Lunak

• Laravel 112- Project API dari hari sebelumnya
• Database - MySQL dengan data dummy minimal 1000 records
• Postman/Thunder Client - Untuk testing parameter query
• Laravel Debugbar - Untuk memonitor query (opsional)
• Visual Studio Code - Editor kode
• Git Bash/Terminal - Untuk menjalankan perintah Artisan

2.2 Perangkat Keras

• Laptop dengan spesifikasi standar

---

3. PEMBAHASAN

3.1 Membuat Data Dummy untuk Pengujian

Sebelum mempelajari pagination dan filtering, kita perlu data yang cukup banyak untuk pengujian.

3.1.1 Factory untuk Post

php

<?php
// File: database/factories/PostFactory.php

namespace Database\Factories;

use App\Models\Post;
use App\Models\User;
use App\Models\Category;
use Illuminate\Database\Eloquent\Factories\Factory;

class PostFactory extends Factory
{
    protected $model = Post::class;

    public function definition(): array
    {
        $title = $this->faker->unique()->sentence(6);
        
        return [
            'title' => $title,
            'slug' => \Str::slug($title),
            'content' => $this->faker->paragraphs(10, true),
            'image' => $this->faker->imageUrl(800, 400),
            'category_id' => Category::inRandomOrder()->first()->id ?? 1,
            'user_id' => User::inRandomOrder()->first()->id ?? 1,
            'views' => $this->faker->numberBetween(0, 10000),
            'is_published' => $this->faker->boolean(80),
            'published_at' => $this->faker->dateTimeBetween('-1 year', 'now'),
            'created_at' => $this->faker->dateTimeBetween('-1 year', 'now'),
            'updated_at' => function (array $attributes) {
                return $this->faker->dateTimeBetween($attributes['created_at'], 'now');
            }
        ];
    }
    
    /**
     * Indikator bahwa post sudah dipublish
     */
    public function published(): static
    {
        return $this->state(fn (array $attributes) => [
            'is_published' => true,
            'published_at' => now(),
        ]);
    }
    
    /**
     * Indikator bahwa post masih draft
     */
    public function draft(): static
    {
        return $this->state(fn (array $attributes) => [
            'is_published' => false,
            'published_at' => null,
        ]);
    }
    
    /**
     * Post dengan views tinggi
     */
    public function popular(): static
    {
        return $this->state(fn (array $attributes) => [
            'views' => $this->faker->numberBetween(5000, 50000),
        ]);
    }
}

3.1.2 Seeder untuk Membuat Data Massal

php

<?php
// File: database/seeders/DatabaseSeeder.php

namespace Database\Seeders;

use App\Models\Post;
use App\Models\Tag;
use App\Models\Comment;
use App\Models\User;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    public function run(): void
    {
        // Buat user
        User::factory(10)->create();
        
        // Buat kategori
        $this->call(CategorySeeder::class);
        
        // Buat tags
        Tag::factory(20)->create();
        
        // Buat 1000 posts
        Post::factory(1000)
            ->create()
            ->each(function ($post) {
                // Attach random tags (1-5 tags per post)
                $tagIds = Tag::inRandomOrder()->limit(rand(1, 5))->pluck('id');
                $post->tags()->attach($tagIds);
                
                // Buat komentar untuk post (0-20 komentar per post)
                Comment::factory(rand(0, 20))->create([
                    'commentable_id' => $post->id,
                    'commentable_type' => Post::class,
                    'user_id' => User::inRandomOrder()->first()->id
                ]);
            });
    }
}

bash

# Jalankan seeder
php artisan db:seed

# Atau refresh dan seed
php artisan migrate:fresh --seed

3.2 Pagination Dasar

Pagination adalah teknik membagi hasil query menjadi beberapa halaman.

3.2.1 Pagination Sederhana dengan paginate()

php

<?php
// File: app/Http/Controllers/Api/PostController.php

use App\Http\Resources\PostResource;

class PostController extends Controller
{
    use ApiResponseTrait;
    
    /**
     * GET /api/posts
     * Dengan pagination sederhana
     */
    public function index(Request $request)
    {
        // Default 15 item per halaman
        $perPage = $request->get('per_page', 15);
        
        // Batasi maksimal 100 item per halaman (anti abuse)
        $perPage = min($perPage, 100);
        
        $posts = Post::with(['author', 'category', 'tags'])
                    ->withCount('comments')
                    ->latest()
                    ->paginate($perPage);
        
        // Return dengan resource collection
        return PostResource::collection($posts);
    }
}

3.2.2 Struktur Response Pagination

Response yang dihasilkan secara otomatis oleh Laravel:

json

{
    "data": [
        { "id": 1, "title": "Post 1", ... },
        { "id": 2, "title": "Post 2", ... },
        ...
    ],
    "links": {
        "first": "http://api.example.com/posts?page=1",
        "last": "http://api.example.com/posts?page=67",
        "prev": null,
        "next": "http://api.example.com/posts?page=2"
    },
    "meta": {
        "current_page": 1,
        "from": 1,
        "last_page": 67,
        "links": [...],
        "path": "http://api.example.com/posts",
        "per_page": 15,
        "to": 15,
        "total": 1000
    }
}

3.2.3 Custom Pagination Resource

Kita bisa mengkustomisasi struktur response pagination:

php

<?php
// File: app/Http/Resources/PostCollection.php

namespace App\Http\Resources;

use Illuminate\Http\Request;
use Illuminate\Http\Resources\Json\ResourceCollection;

class PostCollection extends ResourceCollection
{
    /**
     * Transform the resource collection into an array.
     */
    public function toArray(Request $request): array
    {
        return [
            'data' => PostResource::collection($this->collection),
            'pagination' => [
                'current_page' => $this->currentPage(),
                'total_pages' => $this->lastPage(),
                'total_items' => $this->total(),
                'items_per_page' => $this->perPage(),
                'has_more_pages' => $this->hasMorePages(),
                'has_previous_page' => $this->previousPageUrl() !== null,
                'has_next_page' => $this->nextPageUrl() !== null,
                'first_item' => $this->firstItem(),
                'last_item' => $this->lastItem(),
            ],
            'links' => [
                'first' => $this->url(1),
                'last' => $this->url($this->lastPage()),
                'prev' => $this->previousPageUrl(),
                'next' => $this->nextPageUrl(),
            ],
            'meta' => [
                'timestamp' => now()->toIso8601String(),
                'version' => '1.0.0'
            ]
        ];
    }
    
    /**
     * Customize the response for the collection.
     */
    public function withResponse($request, $response)
    {
        // Menambahkan header pagination custom
        $response->header('X-Total-Count', $this->total());
        $response->header('X-Per-Page', $this->perPage());
        $response->header('X-Current-Page', $this->currentPage());
    }
}

Penggunaan di Controller:

php

public function index(Request $request)
{
    $perPage = $request->get('per_page', 15);
    $perPage = min($perPage, 100);
    
    $posts = Post::with(['author', 'category', 'tags'])
                ->withCount('comments')
                ->latest()
                ->paginate($perPage);
    
    return new PostCollection($posts);
}

3.2.4 Simple Pagination (Hanya Next/Prev)

Untuk API mobile dengan infinite scroll, kita bisa menggunakan simple pagination:

php

public function index(Request $request)
{
    $perPage = $request->get('per_page', 15);
    
    // simplePaginate tidak mengembalikan total count
    $posts = Post::latest()->simplePaginate($perPage);
    
    return response()->json([
        'data' => PostResource::collection($posts),
        'has_more' => $posts->hasMorePages(),
        'next_cursor' => $posts->nextCursor()?->encode(),
        'prev_cursor' => $posts->previousCursor()?->encode()
    ]);
}

3.2.5 Cursor Pagination

Cursor pagination menggunakan "cursor" (token) untuk navigasi, lebih efisien untuk dataset besar:

php

public function index(Request $request)
{
    $perPage = $request->get('per_page', 15);
    
    $posts = Post::orderBy('id')->cursorPaginate($perPage);
    
    return response()->json([
        'data' => PostResource::collection($posts),
        'next_cursor' => $posts->nextCursor()?->encode(),
        'prev_cursor' => $posts->previousCursor()?->encode(),
        'path' => $posts->path()
    ]);
}

Penggunaan:

text

GET /api/posts?cursor=eyJpZCI6MTUsIl9wb2ludHNUb05leHRJdGVtcyI6dHJ1ZX0

3.3 Filtering dan Searching

Filtering memungkinkan client meminta data spesifik berdasarkan kriteria tertentu.

3.3.1 Basic Filtering

php

public function index(Request $request)
{
    $query = Post::with(['author', 'category', 'tags'])
                 ->withCount('comments');
    
    // Filter berdasarkan kategori
    if ($request->has('category_id')) {
        $query->where('category_id', $request->category_id);
    }
    
    // Filter berdasarkan category slug
    if ($request->has('category')) {
        $query->whereHas('category', function ($q) use ($request) {
            $q->where('slug', $request->category);
        });
    }
    
    // Filter berdasarkan author/user
    if ($request->has('author_id')) {
        $query->where('user_id', $request->author_id);
    }
    
    if ($request->has('author')) {
        $query->whereHas('author', function ($q) use ($request) {
            $q->where('name', 'LIKE', '%' . $request->author . '%');
        });
    }
    
    // Filter berdasarkan status publish
    if ($request->has('published')) {
        $published = filter_var($request->published, FILTER_VALIDATE_BOOLEAN);
        if ($published) {
            $query->where('is_published', true)
                  ->whereNotNull('published_at');
        } else {
            $query->where('is_published', false);
        }
    }
    
    // Filter berdasarkan rentang tanggal
    if ($request->has('from_date')) {
        $query->whereDate('created_at', '>=', $request->from_date);
    }
    
    if ($request->has('to_date')) {
        $query->whereDate('created_at', '<=', $request->to_date);
    }
    
    // Filter berdasarkan views
    if ($request->has('min_views')) {
        $query->where('views', '>=', $request->min_views);
    }
    
    if ($request->has('max_views')) {
        $query->where('views', '<=', $request->max_views);
    }
    
    // Search berdasarkan judul atau konten
    if ($request->has('search')) {
        $search = $request->search;
        $query->where(function ($q) use ($search) {
            $q->where('title', 'LIKE', "%{$search}%")
              ->orWhere('content', 'LIKE', "%{$search}%");
        });
    }
    
    // Filter berdasarkan tags
    if ($request->has('tags')) {
        $tagIds = explode(',', $request->tags);
        $query->whereHas('tags', function ($q) use ($tagIds) {
            $q->whereIn('tags.id', $tagIds);
        }, '=', count($tagIds)); // Post harus memiliki semua tag tersebut
    }
    
    // Filter berdasarkan "any tags"
    if ($request->has('any_tag')) {
        $tagIds = explode(',', $request->any_tag);
        $query->whereHas('tags', function ($q) use ($tagIds) {
            $q->whereIn('tags.id', $tagIds);
        });
    }
    
    // Sorting
    $sortField = $request->get('sort_by', 'created_at');
    $sortOrder = $request->get('order', 'desc');
    
    // Validasi field yang boleh di-sort
    $allowedSortFields = ['id', 'title', 'created_at', 'views', 'comments_count'];
    
    if (in_array($sortField, $allowedSortFields)) {
        if ($sortField === 'comments_count') {
            // Jika sort by comments_count, kita perlu memastikan sudah di-withCount
            $query->orderBy('comments_count', $sortOrder);
        } else {
            $query->orderBy($sortField, $sortOrder);
        }
    }
    
    $perPage = $request->get('per_page', 15);
    $perPage = min($perPage, 100);
    
    $posts = $query->paginate($perPage);
    
    return new PostCollection($posts);
}

3.3.2 Membuat Filter Class (Clean Architecture)

Untuk menjaga controller tetap bersih, kita bisa membuat class filter khusus:

bash

php artisan make:class Filters/PostFilter

php

<?php
// File: app/Filters/PostFilter.php

namespace App\Filters;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;

class PostFilter
{
    protected $request;
    protected $query;
    
    public function __construct(Request $request)
    {
        $this->request = $request;
    }
    
    /**
     * Apply all filters to the query
     */
    public function apply(Builder $query): Builder
    {
        $this->query = $query;
        
        foreach ($this->filters() as $filter => $value) {
            if (method_exists($this, $filter)) {
                $this->$filter($value);
            }
        }
        
        return $this->query;
    }
    
    /**
     * Get all filters from request
     */
    public function filters(): array
    {
        return $this->request->all();
    }
    
    /**
     * Filter by category_id
     */
    protected function category_id($value)
    {
        if ($value) {
            $this->query->where('category_id', $value);
        }
    }
    
    /**
     * Filter by category slug
     */
    protected function category($value)
    {
        if ($value) {
            $this->query->whereHas('category', function ($q) use ($value) {
                $q->where('slug', $value);
            });
        }
    }
    
    /**
     * Filter by author_id
     */
    protected function author_id($value)
    {
        if ($value) {
            $this->query->where('user_id', $value);
        }
    }
    
    /**
     * Filter by author name
     */
    protected function author($value)
    {
        if ($value) {
            $this->query->whereHas('author', function ($q) use ($value) {
                $q->where('name', 'LIKE', "%{$value}%");
            });
        }
    }
    
    /**
     * Filter by published status
     */
    protected function published($value)
    {
        $published = filter_var($value, FILTER_VALIDATE_BOOLEAN);
        
        if ($published) {
            $this->query->where('is_published', true)
                        ->whereNotNull('published_at');
        } else {
            $this->query->where('is_published', false);
        }
    }
    
    /**
     * Filter by date range
     */
    protected function from_date($value)
    {
        if ($value) {
            $this->query->whereDate('created_at', '>=', $value);
        }
    }
    
    protected function to_date($value)
    {
        if ($value) {
            $this->query->whereDate('created_at', '<=', $value);
        }
    }
    
    /**
     * Filter by views range
     */
    protected function min_views($value)
    {
        if ($value && is_numeric($value)) {
            $this->query->where('views', '>=', $value);
        }
    }
    
    protected function max_views($value)
    {
        if ($value && is_numeric($value)) {
            $this->query->where('views', '<=', $value);
        }
    }
    
    /**
     * Search in title and content
     */
    protected function search($value)
    {
        if ($value) {
            $this->query->where(function ($q) use ($value) {
                $q->where('title', 'LIKE', "%{$value}%")
                  ->orWhere('content', 'LIKE', "%{$value}%");
            });
        }
    }
    
    /**
     * Filter by tags (must have all tags)
     */
    protected function tags($value)
    {
        if ($value) {
            $tagIds = explode(',', $value);
            $this->query->whereHas('tags', function ($q) use ($tagIds) {
                $q->whereIn('tags.id', $tagIds);
            }, '=', count($tagIds));
        }
    }
    
    /**
     * Filter by any tag
     */
    protected function any_tag($value)
    {
        if ($value) {
            $tagIds = explode(',', $value);
            $this->query->whereHas('tags', function ($q) use ($tagIds) {
                $q->whereIn('tags.id', $tagIds);
            });
        }
    }
    
    /**
     * Sorting
     */
    protected function sort_by($value)
    {
        $allowedFields = ['id', 'title', 'created_at', 'views', 'comments_count'];
        $order = $this->request->get('order', 'desc');
        
        if (in_array($value, $allowedFields)) {
            if ($value === 'comments_count') {
                $this->query->orderBy('comments_count', $order);
            } else {
                $this->query->orderBy($value, $order);
            }
        }
    }
}

Penggunaan di Controller:

php

use App\Filters\PostFilter;

public function index(Request $request, PostFilter $filter)
{
    $posts = Post::with(['author', 'category', 'tags'])
                ->withCount('comments')
                ->filter($filter) // Gunakan macro (akan kita buat)
                ->paginate($request->get('per_page', 15));
    
    return new PostCollection($posts);
}

Tambahkan macro di AppServiceProvider:

php

<?php
// File: app/Providers/AppServiceProvider.php

use Illuminate\Database\Eloquent\Builder;

public function boot()
{
    Builder::macro('filter', function ($filter) {
        return $filter->apply($this);
    });
}

3.3.3 Filtering dengan Spatie Query Builder

Alternatif yang lebih powerful adalah menggunakan package Spatie Query Builder:

bash

composer require spatie/laravel-query-builder

php

<?php
// File: app/Http/Controllers/Api/PostController.php

use Spatie\QueryBuilder\QueryBuilder;
use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\AllowedSort;

public function index(Request $request)
{
    $posts = QueryBuilder::for(Post::class)
                ->with(['author', 'category', 'tags'])
                ->withCount('comments')
                ->allowedFilters([
                    'title',
                    AllowedFilter::exact('id'),
                    AllowedFilter::exact('category_id'),
                    AllowedFilter::exact('user_id', 'author_id'),
                    AllowedFilter::partial('content'),
                    AllowedFilter::scope('published'),
                    AllowedFilter::scope('popular', 'min_views'),
                    AllowedFilter::scope('created_between'),
                    AllowedFilter::callback('search', function ($query, $value) {
                        $query->where(function ($q) use ($value) {
                            $q->where('title', 'LIKE', "%{$value}%")
                              ->orWhere('content', 'LIKE', "%{$value}%");
                        });
                    })
                ])
                ->allowedSorts([
                    'id', 'title', 'created_at', 'views', 
                    AllowedSort::field('comments', 'comments_count')
                ])
                ->defaultSort('-created_at')
                ->paginate($request->get('per_page', 15));
    
    return PostResource::collection($posts);
}

Contoh Penggunaan:

text

# Filter berdasarkan kategori
GET /api/posts?filter[category_id]=1

# Filter berdasarkan search
GET /api/posts?filter[search]=laravel

# Filter dengan multiple kondisi
GET /api/posts?filter[category_id]=1&filter[search]=api&filter[published]=1

# Sorting
GET /api/posts?sort=-views,title

# Include relasi
GET /api/posts?include=author,category,tags

3.4 Advanced Filtering Techniques

3.4.1 Filter dengan Scope di Model

php

<?php
// File: app/Models/Post.php

class Post extends Model
{
    // ...
    
    /**
     * Scope untuk filter berdasarkan status publish
     */
    public function scopePublished($query, $value = true)
    {
        if (filter_var($value, FILTER_VALIDATE_BOOLEAN)) {
            return $query->where('is_published', true)
                        ->whereNotNull('published_at');
        }
        
        return $query->where('is_published', false);
    }
    
    /**
     * Scope untuk filter dengan views minimal
     */
    public function scopePopular($query, $minViews = 1000)
    {
        return $query->where('views', '>=', $minViews);
    }
    
    /**
     * Scope untuk filter rentang tanggal
     */
    public function scopeCreatedBetween($query, $dates)
    {
        if (is_array($dates) && count($dates) === 2) {
            return $query->whereDate('created_at', '>=', $dates[0])
                        ->whereDate('created_at', '<=', $dates[1]);
        }
        
        return $query;
    }
    
    /**
     * Scope untuk filter berdasarkan tags
     */
    public function scopeHasTags($query, $tagIds)
    {
        $tagIds = is_array($tagIds) ? $tagIds : explode(',', $tagIds);
        
        return $query->whereHas('tags', function ($q) use ($tagIds) {
            $q->whereIn('tags.id', $tagIds);
        });
    }
    
    /**
     * Scope untuk filter dengan semua tags tertentu
     */
    public function scopeHasAllTags($query, $tagIds)
    {
        $tagIds = is_array($tagIds) ? $tagIds : explode(',', $tagIds);
        
        foreach ($tagIds as $tagId) {
            $query->whereHas('tags', function ($q) use ($tagId) {
                $q->where('tags.id', $tagId);
            });
        }
        
        return $query;
    }
}

3.4.2 Filter untuk Relasi

php

public function index(Request $request)
{
    $query = Post::with(['author', 'category', 'tags']);
    
    // Filter posts berdasarkan nama author
    if ($request->has('author_name')) {
        $query->whereHas('author', function ($q) use ($request) {
            $q->where('name', 'LIKE', '%' . $request->author_name . '%');
        });
    }
    
    // Filter posts berdasarkan role author
    if ($request->has('author_role')) {
        $query->whereHas('author', function ($q) use ($request) {
            $q->where('role', $request->author_role);
        });
    }
    
    // Filter posts berdasarkan nama kategori
    if ($request->has('category_name')) {
        $query->whereHas('category', function ($q) use ($request) {
            $q->where('name', 'LIKE', '%' . $request->category_name . '%');
        });
    }
    
    // Filter posts berdasarkan nama tag
    if ($request->has('tag_name')) {
        $query->whereHas('tags', function ($q) use ($request) {
            $q->where('name', 'LIKE', '%' . $request->tag_name . '%');
        });
    }
    
    // Filter posts berdasarkan jumlah komentar
    if ($request->has('min_comments')) {
        $query->has('comments', '>=', $request->min_comments);
    }
    
    // Filter posts berdasarkan "memiliki komentar"
    if ($request->has('has_comments')) {
        if (filter_var($request->has_comments, FILTER_VALIDATE_BOOLEAN)) {
            $query->has('comments');
        } else {
            $query->doesntHave('comments');
        }
    }
    
    $posts = $query->paginate($request->get('per_page', 15));
    
    return new PostCollection($posts);
}

3.5 Sorting

3.5.1 Multi-Column Sorting

php

public function index(Request $request)
{
    $query = Post::query();
    
    // Multi-column sorting
    if ($request->has('sort')) {
        $sorts = explode(',', $request->sort);
        
        foreach ($sorts as $sort) {
            $direction = 'asc';
            if (str_starts_with($sort, '-')) {
                $direction = 'desc';
                $sort = substr($sort, 1);
            }
            
            // Validasi field yang diizinkan
            if (in_array($sort, ['id', 'title', 'created_at', 'views'])) {
                $query->orderBy($sort, $direction);
            }
        }
    } else {
        // Default sorting
        $query->latest();
    }
    
    $posts = $query->paginate(15);
    
    return PostResource::collection($posts);
}

Contoh Penggunaan:

text

GET /api/posts?sort=-views,title
# Artinya: sort by views DESC, lalu title ASC

3.5.2 Random Sorting (Untuk Fitur "Random Posts")

php

public function random()
{
    // Cara 1: inRandomOrder() - bisa lambat untuk dataset besar
    $posts = Post::inRandomOrder()->limit(5)->get();
    
    // Cara 2: random dengan performance lebih baik
    $count = Post::count();
    $randomIds = [];
    
    for ($i = 0; $i < 5; $i++) {
        $randomIds[] = rand(1, $count);
    }
    
    $posts = Post::whereIn('id', $randomIds)->get();
    
    return PostResource::collection($posts);
}

3.6 Query Optimization

3.6.1 Menggunakan Explain untuk Analisis Query

php

// Di controller, untuk debugging
public function debugQuery()
{
    $query = Post::where('views', '>', 1000)
                 ->with('author')
                 ->orderBy('created_at', 'desc');
    
    // Dapatkan SQL dengan bindings
    $sql = $query->toSql();
    $bindings = $query->getBindings();
    
    // Jalankan EXPLAIN
    $explain = DB::select('EXPLAIN ' . $sql, $bindings);
    
    return response()->json([
        'sql' => $sql,
        'bindings' => $bindings,
        'explain' => $explain
    ]);
}

3.6.2 Indexing untuk Performa

Buat migration untuk menambahkan index:

bash

php artisan make:migration add_indexes_to_posts_table

php

public function up()
{
    Schema::table('posts', function (Blueprint $table) {
        // Index untuk kolom yang sering di-filter
        $table->index('category_id');
        $table->index('user_id');
        $table->index('is_published');
        $table->index('views');
        $table->index('created_at');
        
        // Composite index untuk kombinasi filter
        $table->index(['category_id', 'is_published', 'created_at']);
    });
}

public function down()
{
    Schema::table('posts', function (Blueprint $table) {
        $table->dropIndex(['category_id']);
        $table->dropIndex(['user_id']);
        $table->dropIndex(['is_published']);
        $table->dropIndex(['views']);
        $table->dropIndex(['created_at']);
        $table->dropIndex(['category_id', 'is_published', 'created_at']);
    });
}

3.6.3 Query Caching untuk Data yang Jarang Berubah

php

public function index(Request $request)
{
    // Buat cache key berdasarkan parameter
    $cacheKey = 'posts_' . md5(json_encode($request->all()));
    
    $posts = Cache::remember($cacheKey, now()->addMinutes(10), function () use ($request) {
        return Post::with(['author', 'category'])
                  ->filter($request)
                  ->paginate($request->get('per_page', 15));
    });
    
    return PostResource::collection($posts);
}

// Clear cache saat data berubah
public function store(StorePostRequest $request)
{
    $post = Post::create($request->validated());
    
    // Hapus cache yang berkaitan
    Cache::flush(); // Atau lebih selektif
    
    return new PostResource($post);
}

3.6.4 Chunking untuk Data Besar

Jika perlu memproses data besar, gunakan chunk:

php

// Contoh: update semua post dengan operasi berat
Post::chunk(100, function ($posts) {
    foreach ($posts as $post) {
        // Lakukan sesuatu dengan setiap post
        $post->update(['processed' => true]);
    }
});

// Dengan cursor (lebih hemat memory untuk iterasi)
foreach (Post::cursor() as $post) {
    // Proses satu per satu tanpa memory besar
}

3.6.5 Select Specific Columns

Jangan mengambil semua kolom jika tidak diperlukan:

php

// BURUK - mengambil semua kolom
$posts = Post::all();

// BAIK - hanya kolom yang diperlukan
$posts = Post::select('id', 'title', 'slug', 'created_at')->get();

// Untuk API list, tidak perlu konten lengkap
public function index()
{
    $posts = Post::select('id', 'title', 'slug', 'excerpt', 'views', 'created_at')
                 ->with('author:id,name,email')
                 ->paginate(15);
    
    return PostResource::collection($posts);
}

3.7 Advanced Pagination dengan Metadata

3.7.1 Menambahkan Statistik ke Response

php

public function index(Request $request)
{
    $query = Post::query();
    
    // Dapatkan total sebelum pagination untuk statistik
    $totalAll = $query->count();
    $totalPublished = $query->clone()->where('is_published', true)->count();
    $totalDraft = $query->clone()->where('is_published', false)->count();
    
    $posts = $query->with(['author', 'category'])
                  ->latest()
                  ->paginate($request->get('per_page', 15));
    
    return response()->json([
        'data' => PostResource::collection($posts),
        'pagination' => [
            'current_page' => $posts->currentPage(),
            'per_page' => $posts->perPage(),
            'total' => $posts->total(),
            'last_page' => $posts->lastPage()
        ],
        'stats' => [
            'total_all' => $totalAll,
            'total_published' => $totalPublished,
            'total_draft' => $totalDraft,
            'published_percentage' => $totalAll > 0 ? round(($totalPublished / $totalAll) * 100) : 0
        ],
        'filters' => $request->all() // Echo back filters yang digunakan
    ]);
}

3.7.2 Range Pagination (Halaman Spesifik)

php

public function range(Request $request)
{
    $start = $request->get('start', 1);
    $end = $request->get('end', 10);
    
    if ($start > $end) {
        return $this->errorResponse('Start harus lebih kecil dari end', 422);
    }
    
    $perPage = $end - $start + 1;
    $page = ceil($start / $perPage);
    
    $posts = Post::latest()
                ->paginate($perPage, ['*'], 'page', $page);
    
    // Potong array sesuai range yang diminta
    $offset = ($start - 1) % $perPage;
    $items = $posts->items();
    $slicedItems = array_slice($items, $offset, $end - $start + 1);
    
    return response()->json([
        'data' => PostResource::collection(collect($slicedItems)),
        'range' => [
            'from' => $start,
            'to' => $end,
            'total' => $posts->total()
        ]
    ]);
}

3.8 Testing dengan Postman

3.8.1 Basic Pagination

text

GET {{base_url}}/api/posts?page=2&per_page=10

Response Headers akan berisi:

text

X-Total-Count: 1000
X-Per-Page: 10
X-Current-Page: 2

3.8.2 Filtering

text

# Filter berdasarkan kategori
GET {{base_url}}/api/posts?category_id=1

# Filter berdasarkan author
GET {{base_url}}/api/posts?author_id=5

# Filter berdasarkan status publish
GET {{base_url}}/api/posts?published=true

# Search
GET {{base_url}}/api/posts?search=laravel

# Multiple filter
GET {{base_url}}/api/posts?category_id=1&published=true&search=api

# Filter dengan range
GET {{base_url}}/api/posts?min_views=1000&max_views=5000

3.8.3 Sorting

text

# Sort ascending
GET {{base_url}}/api/posts?sort_by=title&order=asc

# Sort descending
GET {{base_url}}/api/posts?sort_by=views&order=desc

# Multi-column sort (dengan custom parser)
GET {{base_url}}/api/posts?sort=-views,title

3.8.4 Kombinasi Lengkap

text

GET {{base_url}}/api/posts?
    page=2&
    per_page=15&
    category_id=1&
    published=true&
    search=api&
    min_views=100&
    sort_by=views&
    order=desc

3.9 Membuat API Documentation dengan Query Parameters

Buat dokumentasi di controller menggunakan PHPDoc:

php

<?php
// File: app/Http/Controllers/Api/PostController.php

/**
 * @OA\Get(
 *     path="/api/posts",
 *     summary="Get paginated list of posts",
 *     tags={"Posts"},
 *     @OA\Parameter(
 *         name="page",
 *         in="query",
 *         description="Page number",
 *         required=false,
 *         @OA\Schema(type="integer", default=1)
 *     ),
 *     @OA\Parameter(
 *         name="per_page",
 *         in="query",
 *         description="Items per page",
 *         required=false,
 *         @OA\Schema(type="integer", default=15, maximum=100)
 *     ),
 *     @OA\Parameter(
 *         name="category_id",
 *         in="query",
 *         description="Filter by category ID",
 *         required=false,
 *         @OA\Schema(type="integer")
 *     ),
 *     @OA\Parameter(
 *         name="author_id",
 *         in="query",
 *         description="Filter by author ID",
 *         required=false,
 *         @OA\Schema(type="integer")
 *     ),
 *     @OA\Parameter(
 *         name="published",
 *         in="query",
 *         description="Filter by published status",
 *         required=false,
 *         @OA\Schema(type="boolean")
 *     ),
 *     @OA\Parameter(
 *         name="search",
 *         in="query",
 *         description="Search in title and content",
 *         required=false,
 *         @OA\Schema(type="string")
 *     ),
 *     @OA\Parameter(
 *         name="min_views",
 *         in="query",
 *         description="Minimum views",
 *         required=false,
 *         @OA\Schema(type="integer")
 *     ),
 *     @OA\Parameter(
 *         name="sort_by",
 *         in="query",
 *         description="Sort field (id, title, created_at, views)",
 *         required=false,
 *         @OA\Schema(type="string", enum={"id", "title", "created_at", "views"})
 *     ),
 *     @OA\Parameter(
 *         name="order",
 *         in="query",
 *         description="Sort order",
 *         required=false,
 *         @OA\Schema(type="string", enum={"asc", "desc"}, default="desc")
 *     ),
 *     @OA\Response(
 *         response=200,
 *         description="Successful operation"
 *     )
 * )
 */
public function index(Request $request)
{
    // ...
}

3.10 Kendala dan Solusi

Kendala	Solusi
Pagination lambat untuk data besar	Gunakan cursor pagination, tambahkan index
Filter tidak bekerja	Pastikan nama parameter sesuai, gunakan has() untuk debug
N+1 query setelah pagination	Gunakan with() untuk eager loading relasi
Memory usage tinggi	Batasi per_page, gunakan select() kolom spesifik
Sorting berdasarkan kolom relasi	Join tabel atau gunakan withCount() lalu sort
Response terlalu besar	Kompres response, gunakan sparse fields
Cache tidak invalidate	Gunakan cache tags, clear cache saat data berubah
Filter OR/AND kompleks	Gunakan query builder dengan grouping

---

4. KESIMPULAN

Pagination, filtering, dan query optimization adalah fondasi API yang scalable dan profesional. Pada hari ini, kita telah mempelajari:

1. Pagination
• paginate()
• untuk pagination lengkap dengan metadata
• simplePaginate()
• untuk infinite scroll
• cursorPaginate()
• untuk performa terbaik dengan dataset besar
• Custom pagination resource dengan metadata tambahan
2. Filtering
• Basic filtering dengan kondisi WHERE
• Filter class untuk kode yang bersih dan terstruktur
• Spatie Query Builder untuk filtering yang powerful
• Filter berdasarkan relasi dengan whereHas()
3. Sorting
• Single dan multi-column sorting
• Sorting berdasarkan kolom relasi dengan withCount()
• Random sorting untuk fitur khusus
4. Query Optimization
• Indexing untuk mempercepat query
• Eager loading untuk menghindari N+1
• Query caching untuk data statis
• Select specific columns
• Chunking untuk data besar
5. Response Format
• Metadata pagination yang informatif
• Statistik tambahan
• Echo back filters untuk debugging

Dengan teknik-teknik ini, API kita siap melayani ribuan request per detik dengan data jutaan records. Pada hari ketiga, kita akan mempelajari File Upload API & Storage Management untuk menangani upload gambar, video, dan file lainnya.

---

5. DAFTAR PUSTAKA

• Laravel Official Documentation. (n.d.). Laravel 12.x Documentation - Pagination. https://laravel.com/docs/12.x/pagination
• Laravel Official Documentation. (n.d.). Laravel 12.x Documentation - Queries. https://laravel.com/docs/12.x/queries
• Laravel Official Documentation. (n.d.). Laravel 12.x Documentation - Eloquent: Serialization. https://laravel.com/docs/12.x/eloquent-serialization
• Spatie. (n.d.). Laravel Query Builder Documentation. https://spatie.be/docs/laravel-query-builder