Paginating Large Lists Safely with CodeIgniter 4’s Model and Query Builder

Problem: manual LIMIT/OFFSET leads to bugs and injection risk

When you need to show a list of products, users, or logs, writing your own LIMIT and OFFSET SQL is error-prone. A mistyped page number can produce negative offsets, and concatenating user-supplied search terms opens the door to SQL injection. CodeIgniter 4 offers a built-in way to avoid both issues by letting the Model class work directly with the Query Builder and the Pager service.

Thesis: use Model::paginate() for safe, consistent pagination

The Model wrapper gives you a single connection, automatic identifier escaping, and value binding. Its paginate($perPage) method runs a COUNT(*) query plus a limited SELECT, then returns the rows for the current page. The Pager service renders navigation links from the ?page=N query parameter, which the framework validates, eliminating classic off-by-one bugs.

Worked example: a product catalogue

Assume a products table with columns id, name, active and price. We’ll create a model, controller, and view that show 20 active products per page.

1. Model – app/Models/ProductsModel.php

namespace App\Models;

use CodeIgniter\Model;

class ProductsModel extends Model
{
    protected $table      = 'products';
    protected $primaryKey = 'id';
    
    // Only allow these fields to be mass-assigned (helps with validation)
    protected $allowedFields = ['name', 'active', 'price'];
    
    // Optional: turn timestamps on/off if your table has them
    protected $useTimestamps = false;
}

2. Controller – app/Controllers/Product.php

namespace App\Controllers;

use App\Models\ProductsModel;

class Product extends BaseController
{
    public function index()
    {
        $model = new ProductsModel();
        
        // Example filter: only active products
        $model->where('active', 1);
        
        // Paginate 20 rows per page; page number comes from ?page=
        $data['products'] = $model->paginate(20);
        
        // The pager instance is automatically added to $data by the model
        $data['pager']    = $model->pager;
        
        return view('product/list', $data);
    }
}

3. View – app/views/product/list.php

<h1>Product Catalogue</h1>
<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Price</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($products as $p): ?>
        <tr>
            <td><?= esc($p['id']) ?></td>
            <td><?= esc($p['name']) ?></td>
            <td><?= esc($p['price']) ?></td>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>

<!-- Pager links -->
<?= $pager->links() ?>

When you visit /product?page=2, the framework:

1. Validates that page=2 is a positive integer.
2. Runs a COUNT(*) query over the filtered table to know the total rows.
3. Runs the limited select with the appropriate LIMIT and OFFSET to fetch the second page.
4. Returns the rows to the view and supplies a Pager object for $pager->links().

Trade-off: extra COUNT query and OFFSET cost

The convenience comes with two considerations:

• Extra COUNT query – each request issues a COUNT(*) in addition to the limited select. On very large tables or when complex WHERE clauses are added, this can add noticeable latency.
• OFFSET degradation – deep pages (e.g., page 500 with 20 rows per page) still use a large OFFSET, which forces the database to scan and skip many rows. For huge datasets, keyset (cursor) pagination using a WHERE id > :last_seen_id pattern scales better.

If you notice the COUNT query becoming a bottleneck (you can see it in the debug toolbar or by enabling database query logging), consider:

1. Adding appropriate indexes on the filtered columns.
2. Switching to a custom query that uses keyset pagination for the specific use case.
3. Caching the count result when the underlying data changes infrequently.

Limitation: user-supplied sort columns need whitelisting

Query Builder safely escapes values, but it does not protect against arbitrary identifiers. Allowing a user to dictate the ORDER BY column via ?sort=price is safe only if you whitelist the allowed column names:

$allowedSort = ['name', 'price', 'id'];
$sort = $this->request->getGet('sort') ?? 'name';
if (! in_array($sort, $allowedSort, true)) {
    $sort = 'name'; // fallback to a safe default
}
$model->orderBy($sort, 'asc');

Actionable closing

For most admin listings, blogs, or product catalogues where the dataset fits comfortably in memory and page depth stays low, Model::paginate() gives you:

• Automatic SQL injection protection for values.
• Consistent escaping of identifiers.
• Built-in validation of the page parameter, removing off-by-one bugs.
• Simple view integration with $pager->links().

Start by adding the model, controller, and view snippets above, then verify the generated queries with the debug toolbar. Note this targets CodeIgniter 4.x; CodeIgniter 3 used a different pagination library, so the code is not interchangeable. Confirm the Model and Pager APIs against your installed version (composer show codeigniter4/framework) before publishing. If you later hit performance walls on the COUNT or OFFSET, evaluate keyset pagination or cached counts as the next step.