Efficient Data Retrieval in CodeIgniter 4: Query Builder + Pagination

Requirements

To implement a scalable list view you need:

• A database table with a primary key – preferably an auto‑incrementing integer.
• CodeIgniter 4 installed (v4.4+ recommended for the built‑in paginate() helper).
• Pagination library enabled in app/Config/Pagination.php (default settings are usually sufficient).
• Optional: a cache driver (file, Redis, or Memcached) for read‑heavy pages.
• Basic understanding of MVC: a controller method that builds a query and passes results to a view.

Smallest Suitable Design

The core of the pattern is a single controller method that:

1. Validates the page query parameter.
2. Builds a query with Query Builder.
3. Calls paginate() to apply LIMIT and OFFSET.
4. Renders the result set with Pager links.

Below is a minimal, production‑ready example.

namespace App\Controllers;

use CodeIgniter\Controller;
use Config\Services;

class UserList extends Controller
{
    public function index()
    {
        // 1. Validate page number – default to 1 if missing or invalid
        $page = (int) ($this->request->getGet('page') ?? 1);
        if ($page < 1) {
            $page = 1;
        }

        // 2. Optional: cache key for the current page
        $cache = Services::cache();
        $cacheKey = 'users_page_' . $page;
        $data = $cache->get($cacheKey);

        if ($data === null) {
            // 3. Build the query – only the columns you need
            $builder = db_connect()
                ->table('users')
                ->select('id, username, email, created_at')
                ->orderBy('created_at', 'DESC');

            // 4. Apply pagination – 20 items per page
            $pager = $builder->paginate(20, 'default', null, $page);

            // 5. Store result set in cache for 5 minutes
            $cache->save($cacheKey, $pager, 300);
            $data = $pager;
        }

        // 6. Pass data to view
        return view('users/list', [
            'users' => $data,
            'pager' => $pager,
        ]);
    }
}

Key points:

• The paginate() helper automatically calculates LIMIT and OFFSET and stores the total count for the pager.
• Cache is keyed per page; the timeout should match your data volatility.
• All database interaction is via Query Builder, which protects against injection and abstracts SQL differences.

Trust & Data Boundaries

In a typical CI4 app the controller is the boundary between the HTTP layer and the data layer. By delegating all SQL to Query Builder you keep:

• Model code minimal – if you prefer a model, simply inject the builder into the constructor.
• View code clean – the pager object contains all URL segments and page metadata.
• Security – the builder uses bound parameters; no raw string concatenation.

When you introduce caching, the boundary shifts slightly: you must decide if the cache should be invalidated by the model or by an event listener (e.g., after an insert/update/delete). Keeping invalidation logic in the model or a dedicated service keeps the controller free of cache‑specific code.

Operational Checks

• Page Parameter Validation – Ensure page is an integer and within the total page range. Use FILTER_VALIDATE_INT and compare against $pager->getPageCount().
• SQL Debugging – In development, enable CI_DEBUG and call db_connect()->getLastQuery() to verify LIMIT/OFFSET.
• Cache Hit Ratio – Monitor $cache->getCacheStats() (if supported) or log cache misses to gauge effectiveness.
• Error Handling – Wrap database calls in try/catch; on exception, log the error and show a user‑friendly message.

Failure Modes

Common pitfalls and how to mitigate them:

• Large OFFSET – For tables with millions of rows, OFFSET can become expensive. Switch to keyset pagination: where('id >', $lastId) and order by id ASC.
• Stale Cache – If data changes frequently, a long cache TTL can serve outdated pages. Use event listeners to clear the relevant keys after write operations.
• Broken URLs – When behind a CDN or reverse proxy, ensure base_url() and site_url() are correctly configured so pager links resolve.
• Denial‑of‑Service via Page Flood – Validate page numbers and redirect to the nearest valid page or return a 404 instead of rendering an empty list.

When to Redesign

Trigger a redesign when any of the following conditions are met:

• Query Latency Exceeds 200 ms – Profile the SELECT query with EXPLAIN to check index usage.
• Cache Hit Ratio Drops Below 70 % – Indicates high churn; consider moving to a write‑through cache or reducing TTL.
• User Load > 10,000 concurrent requests – The simple LIMIT/OFFSET pattern may need to be replaced with cursor‑based pagination.
• Database Server Spikes – Frequent cache misses or heavy write traffic can cause lock contention; evaluate a read replica or query optimization.

In such cases, refactor to:

• Use a dedicated repository layer that encapsulates pagination logic.
• Implement keyset pagination for high‑volume tables.
• Introduce a message‑queue based cache invalidation to decouple writes from cache clearing.

Always test the new design against a representative dataset and monitor key metrics (response time, cache hit ratio, DB load) before rolling out to production.