Implementing Database‑Backed Pagination in CakePHP 4/5 with the ORM and Paginator

Desired Outcome

Build an index action that displays a paginated, sortable list of records without loading the entire table into memory. The page should show a configurable number of items per page, allow users to sort by whitelisted columns, and provide navigation links (next, previous, page numbers).

Prerequisites

• A CakePHP 4.x or 5.x application with a working database connection.
• A table (e.g., articles) and its corresponding Table class (e.g., ArticlesTable).
• Composer‑installed dependencies (run composer install if not already done).
• Ensure the Paginator component is available. In 4.x it is loaded via loadComponent; in 5.x it is automatically loaded with the controller’s components array.

Procedure

1. Load the Paginator Component

   In src/Controller/ArticlesController.php add the component in initialize() (CakePHP 4.x) or declare it in the components array (CakePHP 5.x).

   // CakePHP 4.x
   public function initialize(): void
   {
       parent::initialize();
       $this->loadComponent('Paginator');
   }
   
   // CakePHP 5.x – automatically loaded, but can be overridden
   public array $components = ['Paginator'];
   

2. Build the Query

   Use the ORM to construct a query that may include filtering or associations. Keep the query simple to avoid unnecessary joins in the count.

   // Example: filter by status and include author
   $query = $this->Articles->find('all')
       ->where(['status' => 'published'])
       ->contain(['Authors']);
   

3. Configure Pagination Settings

   Define the pagination options. The sortWhitelist (CakePHP 4.x) or sortableFields (CakePHP 5.x) option protects against unsanitized sort parameters. Set a default order and limit.

   // CakePHP 4.x
   $paginate = [
       'limit' => 20,
       'order' => ['Articles.created' => 'desc'],
       'sortWhitelist' => ['title', 'created', 'author_name']
   ];
   
   // CakePHP 5.x
   $paginate = [
       'limit' => 20,
       'order' => ['Articles.created' => 'desc'],
       'sortableFields' => ['title', 'created', 'author_name']
   ];
   

4. Execute Pagination

   Pass the query and options to the paginator. The component will automatically apply LIMIT and OFFSET and store pagination data in the request.

   $articles = $this->paginate($query, $paginate);
   $this->set(compact('articles'));
   

5. Render the View

   Use the Paginator helper to generate sortable column headers and navigation links. The helper reads the pagination metadata set by the component.

   <h1>Articles</h1>
   <table>
       <thead>
           <tr>
               <th>{<?= $this->Paginator->sort('title', 'Title') ?>}</th>
               <th>{<?= $this->Paginator->sort('created', 'Published') ?>}</th>
               <th>{<?= $this->Paginator->sort('author_name', 'Author') ?>}</th>
           </tr>
       </thead>
       <tbody>
           <?= $this->Html->tableCells($articles, ['class' => 'table-row']) ?>
       </tbody>
   </table>
   
   <div class="pagination">
       {<?= $this->Paginator->prev('« Prev') ?>}
       {<?= $this->Paginator->numbers() ?>}
       {<?= $this->Paginator->next('Next »') ?>}
       <p>{<?= $this->Paginator->counter(['format' => 'Page {{page}} of {{pages}}, showing {{current}} record(s) out of {{count}} total.']) ?>}</p>
   </div>
   

Expected Checks

• First page displays limit records (e.g., 20).
• Counter shows correct total count and current page.
• Clicking a sortable header toggles ascending/descending order.
• Next/previous links navigate correctly.
• Requesting a page number beyond the last page results in a NotFoundException (CakePHP 5.x) or redirects to the last page (CakePHP 4.x) unless overridden.

Recovery Options

• Out‑of‑Range Page Numbers: In CakePHP 5.x set defaultPage to 1 or catch the exception:

  try {
      $articles = $this->paginate($query);
  } catch (NotFoundException $e) {
      $this->Flash->error('Page not found.');
      return $this->redirect(['page' => $this->paginate->getPageCount()]);
  }
  

• Incorrect Record Count with Associations: Ensure the query uses distinct on the primary key or supply a separate count query:

  $countQuery = $query->select(['Articles.id'])->distinct(['Articles.id']);
  $paginate = $paginate + ['count' => $countQuery];
  

• Sorting on Computed or Associated Fields: Add the field to sortableFields and, if needed, specify the join or alias in the query.
• Unbounded Limits: Define a maxLimit option to prevent large limit values from user input.

  $paginate['maxLimit'] = 100;
  

Version Differences

Feature	CakePHP 4.x	CakePHP 5.x
Component Loading	loadComponent('Paginator')	automatic via components array
Sort Whitelist Option	sortWhitelist	sortableFields
Out‑of‑Range Page Handling	redirect to last page	NotFoundException
Pagination Options Syntax	array passed to paginate()	array passed to paginate() (same)

Practical Verification

• Run bin/cake server and navigate to /articles to see the first page.
• Open the browser’s network tab or use bin/cake console with debug to view the generated SQL; confirm LIMIT and OFFSET are present.
• Use the DebugKit panel to inspect the pagination metadata stored in the request.
• Write a simple integration test that requests page 1 and asserts HTTP 200 and the expected number of records in the response body.

Limitations

• Pagination counts can be inflated by joins that duplicate rows; use distinct or a separate count query.
• Sorting by fields from associated tables requires explicit joins or aliasing; otherwise the sort will be ignored.
• Large maxLimit values can still cause performance issues if the underlying database cannot handle the query efficiently.

Conclusion

By following the steps above you can add robust, database‑backed pagination to any CakePHP controller. The key is to keep the query lean, whitelist sortable fields, and handle edge cases such as out‑of‑range pages and count inaccuracies. With these safeguards your application will scale gracefully as data grows.