CakePHP Pagination: Query Boundaries, Page Limits, and SQL Verification

Problem & Takeaway

CakePHP’s $this->paginate() accepts a Query object and turns request parameters such as page and limit into LIMIT and OFFSET SQL clauses. If the controller builds that query from raw request input or leaves limit unbounded, a single request can scan millions of rows or inject unsafe filter conditions. The practical takeaway is to scope filtering inside a table-class finder, treat all request parameters as untrusted, enforce a maximum page size in the controller, and verify the generated SQL with DebugKit or query logging before treating the endpoint as production-ready.

Requirements

• CakePHP 4.x LTS (4.4 or later). The examples use the fluent Query builder; CakePHP 3.x array-style find('all', ...) options are not covered.
• PHP 8.1+ and a relational database such as MySQL 8+ or PostgreSQL 13+.
• Access to the application directory where bin/cake can run.
• DebugKit installed, or the built-in logQueries debug option enabled.

Smallest Suitable Design

Put filter logic in a custom finder on the table class so the controller does not assemble conditions from the request. The controller then passes the prepared query to $this->paginate() and sets a maxLimit cap.

// src/Model/Table/ArticlesTable.php
namespace App\Model\Table;

use Cake\ORM\Table;
use Cake\ORM\Query;

class ArticlesTable extends Table
{
    public function findPublished(Query $query, array $options): Query
    {
        return $query
            ->where(['Articles.status' => 'published'])
            ->order(['Articles.created' => 'DESC']);
    }
}

// src/Controller/ArticlesController.php
namespace App\Controller;

use App\Controller\AppController;

class ArticlesController extends AppController
{
    public function index(): void
    {
        $query = $this->Articles->find('published');

        $this->paginate = [
            'limit' => 20,
            'maxLimit' => 100,
        ];

        $articles = $this->paginate($query);

        $this->set(compact('articles'));
    }
}

find('published') invokes the finder defined above. $this->paginate() reads the page parameter from the request, applies LIMIT, and adds OFFSET based on the current page. maxLimit rejects or clamps page sizes above 100 so a malicious or accidental ?limit=100000 cannot force a huge result set.

Trust/Data Boundaries

• Request layer (untrusted): page, limit, and any search terms arrive from the client. Never pass them directly into where() as raw SQL fragments.
• Table layer (trusted boundary): The finder defines static conditions and accepted columns. If a search term is needed, bind it as a parameter: ->where(['Articles.title LIKE' => '%' . $search . '%']). CakePHP converts the value to a bound parameter, which prevents SQL injection.
• Controller layer: Chooses the finder and pagination limits. It should not append request-derived columns to order() without an allow-list.
• View layer: Renders the paginated collection and pagination links. It does not modify the query.

Operational Checks

1. Enable query logging. In config/app.php, under the Debug section, set 'logQueries' => true when debug is true. Alternatively install and load DebugKit to inspect queries in the toolbar.
2. Run the application. From the project root, run bin/cake server. This requires shell access and read permissions on the application files; the web server process needs write access to logs/ if query logging writes to files.
3. Request page one. Visit /articles?page=1. In the DebugKit Queries panel or the application log, confirm the SQL contains LIMIT 20 OFFSET 0.
4. Request page two. Visit /articles?page=2. Confirm OFFSET advances to 20 while LIMIT stays at 20.
5. Test the limit cap. Visit /articles?limit=500. The resulting SQL should show LIMIT 100, not 500, because maxLimit overrides the request value.
6. Inspect the response. The view should render 20 articles per page, and the pagination helper links should reflect the total count returned by the paginator.

If logQueries is enabled but no SQL appears, verify that debug is true in config/app.php and that the log writer is configured to write to logs/debug.log. Query logging is intended for development; leaving it enabled in production can fill disk space and expose query structure.

Failure Modes & Conditions to Change Design

• Deep OFFSET pagination. As page grows, OFFSET 100000 forces the database to scan and discard preceding rows. Response time degrades linearly with page depth. If users routinely page beyond a few hundred results, switch to keyset (cursor) pagination using a unique ordered column such as Articles.id with WHERE Articles.id > :lastId ORDER BY Articles.id ASC LIMIT 20.
• Unindexed filter columns. A WHERE clause on status or created without an index causes a full table scan. Run EXPLAIN in MySQL or EXPLAIN ANALYZE in PostgreSQL against the generated query and add indexes where the database reports a sequential scan.
• Unsafe ordering. Allowing ?order=Articles.dropped_column passed straight to order() can trigger database errors or information leakage. Maintain an allow-list of sortable columns in the controller and map request keys to them before calling order().
• Heavy contain nesting. Eager-loading many associations with contain multiplies the result set and memory use. Limit contain depth and profile queries when associations grow.

Concrete Diagnostic Example

Suppose /articles?page=3&limit=50 returns slowly. Capture the logged query:

SELECT Articles.id AS `Articles__id`, Articles.title AS `Articles__title`
FROM articles Articles
WHERE Articles.status = 'published'
ORDER BY Articles.created DESC
LIMIT 50 OFFSET 100

Run the same SELECT in a database console with EXPLAIN prepended. If the plan shows a full scan on articles, add an index:

CREATE INDEX idx_articles_status_created ON articles (status, created DESC);

Run the request again and confirm the plan now uses the index and response time drops. This is a diagnostic decision, not a schema migration to perform without testing on a staging copy.

Limitations & Practical Verification

• The examples assume CakePHP conventions: table articles, primary key id, and entity class Article. Custom tables require explicit table() or primaryKey() configuration.
• maxLimit clamps the limit option but does not validate page beyond numeric coercion; negative or non-numeric pages fall back to the paginator defaults.
• This design covers HTML page pagination. For JSON APIs, use JsonView and return pagination metadata such as pageCount and count explicitly.
• Verify after any change by repeating the operational checks above: page one shows OFFSET 0, page two shows OFFSET 20, and an oversized limit is clamped to maxLimit.

By keeping filters in the table layer, capping page size in the controller, and checking the generated SQL before deployment, a CakePHP pagination endpoint stays predictable as data volume grows.