CodeIgniter 4 Pagination with Query Builder: How paginate() Works and Where It Breaks

If you need paginated results in CodeIgniter 4, the shortest correct path is $model->paginate($perPage) (or the Query Builder equivalent), which runs both the data query and a count query for you and hands back a ready-made $pager object. The catch is that paginate() makes assumptions about your base URL, your count query, and your table structure — and when any of those assumptions are wrong, you get silent failures like empty page links or wrong offsets rather than errors. This article explains what paginate() actually does, shows a working configuration, and covers the mistakes that most often break it.

What paginate() actually executes

When you call paginate(10) on a Model or through the Query Builder, CodeIgniter does two things. First, it runs a count query (essentially SELECT COUNT(*) with your current WHERE conditions) to determine the total number of matching rows. Second, it reads the current page number from the page query parameter in the URL, computes the offset, and appends LIMIT 10 OFFSET n to your data query. The results come back as the return value of paginate(), and the pagination metadata lives on the model's $pager property.

You can confirm this yourself. After running a paginated query in a controller, add a temporary debug line:

$db = \Config\Database::connect();
$builder = $db->table('articles');
$results = $builder->paginate(10);
// Temporary debug — remove before production
echo $db->getLastQuery();

Run this from any route in your development environment (no special permissions needed beyond normal app access). You should see a SELECT ... LIMIT 10 OFFSET ... statement, and the offset should equal (page - 1) * 10. If the LIMIT/OFFSET is missing, pagination never engaged — usually because you called get() before paginate(), which executes the query early.

A working controller and view

The conventional pattern uses a Model, because the model carries the pager instance for you:

namespace App\Controllers;

use App\Models\ArticleModel;

class Articles extends BaseController
{
    public function index()
    {
        $model = new ArticleModel();

        $data = [
            'articles' => $model->where('published', 1)
                                ->orderBy('created_at', 'DESC')
                                ->paginate(10),
            'pager'    => $model->pager,
        ];

        return view('articles/index', $data);
    }
}

In the view, render the links:

<?php foreach ($articles as $article): ?>
    <h2><?= esc($article['title']) ?></h2>
<?php endforeach; ?>

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

Two details matter here. First, paginate() must be the terminal call in the chain — it executes the query, so anything you chain after it operates on the result array, not the builder. Second, links() generates URLs based on the current request URI by default. If your app sits in a subdirectory or behind a reverse proxy that rewrites paths, the generated hrefs can point at the wrong place. In that case, set the base URL explicitly in the controller:

$model->pager->setPath(site_url('articles'));

Note the method is setPath() on the pager in current CI4 versions — older tutorials reference methods that have been renamed across releases, so check the CodeIgniter\Pager\Pager class in your installed version if a call fails.

Controlling the page link display

The default link template shows a window of page numbers around the current page. You tune this with setSurroundCount(), which controls how many numbered links appear on each side of the current page:

<?= $pager->links('default', 'default_full') ?>

or, per pager instance:

$pager->setSurroundCount(2);

A surround count of 2 on page 7 shows links for pages 5, 6, 7, 8, 9 plus first/previous/next/last controls. You can also build entirely custom templates — copy app/Views/Pagers/default_full.php (or the framework's version under vendor/codeigniter4/framework/src/Pager/Views) into app/Views/Pagers/, modify the markup, and pass the template name as the second argument to links(). This is the supported way to match pagination markup to a CSS framework like Bootstrap or Tailwind.

Where pagination silently breaks

Joins and the count query

The count query is derived from your builder state, and joins are the classic failure point. If you join a table with one-to-many rows (say, articles to comments), the count query can count joined rows rather than distinct articles, inflating the total and producing empty trailing pages. The fix is to make the count explicit. With a model you can supply the total yourself by running your own count and using the pager directly, or restructure the query so the count reflects distinct base-table rows, e.g. with groupBy('articles.id') or a subquery. Verify by comparing:

// In a controller, temporarily:
$total = $model->pager->getTotal();
$manual = $db->table('articles')->where('published', 1)->countAllResults();
// $total and $manual should match for simple queries

If they disagree, your paginated count is wrong and the page links will not reflect reality.

Manual limit/offset instead of paginate()

Some developers build pagination by hand with limit() and offset(). That works for the data query, but you lose the automatic count query and the pager object, so you must reimplement total-row counting, page bounds checking, and link generation yourself. Unless you have a genuinely unusual requirement (cursor-based pagination for very large tables, for example, where OFFSET scans get slow), paginate() is less code and fewer bugs.

Multiple paginated lists on one page

By default every pager reads the same page query parameter, so two paginated tables on one page advance together. Pass a group name as the second argument to keep them independent:

$articles = $articleModel->paginate(10, 'articles');
$comments = $commentModel->paginate(5, 'comments');
// In the view:
<?= $articleModel->pager->links('articles') ?>

Each group then uses its own query parameter (page_articles, page_comments).

Limits and verification

OFFSET-based pagination degrades on very large tables: page 10,000 at 10 rows per page forces the database to scan and discard 100,000 rows. For tables in the millions of rows, consider keyset (cursor) pagination with a WHERE clause on an indexed column instead. Also note that pagination behavior and method names have shifted across CI4 minor releases; this article reflects CodeIgniter 4.x conventions, and you should confirm method signatures against the system/Pager source in your installed version before relying on a specific call.

Practical verification checklist: (1) check getLastQuery() shows the expected LIMIT/OFFSET; (2) load page 2 directly via ?page=2 and confirm the offset and results change; (3) compare $pager->getTotal() against a manual COUNT(*); (4) click every rendered link type (first, previous, numbered, next, last) once. If all four pass, your pagination is wired correctly.