Paging large tables in Laminas MVC with the DbSelect paginator adapter

When a Laminas MVC controller returns thousands of rows, fetching the full result set into PHP memory is the fastest way to exhaust resources. The useful decision is to keep paging on the database side. Laminas\Paginator\Adapter\DbSelect accepts a Laminas\Db\Sql\Select and a database adapter, then rewrites that select for every page with LIMIT and OFFSET. Only the rows for the current page are hydrated, so memory usage stays flat whether the table holds one thousand rows or one million.

How the DbSelect adapter works

The adapter is a thin bridge between the paginator and Laminas DB. It stores the select object and adapter reference at construction. When the paginator needs items, the adapter clones the select, applies the limit and offset for the requested page, and executes it. Separately, it builds a count query from the same select so the paginator can calculate total pages. The paginator itself holds no state; you set the current page number and items per page on the Laminas\Paginator\Paginator instance.

Two assumptions matter. First, the underlying platform must support the LIMIT/OFFSET syntax that the Laminas DB platform generates. Most mainstream relational databases do, but older or embedded engines may require dialect-specific handling. Second, the select should be a read-only query. The adapter captures the select at construction, so mutating it later produces inconsistent pages.

Worked controller configuration

The following pattern shows a typical list action. Replace users, the filter values, and the query parameter name with your own schema. The database adapter is assumed to be available through the controller; in a laminas-mvc application it is commonly retrieved from the service manager or injected.

// src/Controller/UserController.php
public function listAction()
{
    $page = (int) $this->params()->fromQuery('page', 1);
    $page = max($page, 1);
    $pageSize = 20;

    /** @var \Laminas\Db\Adapter\Adapter $dbAdapter */
    $dbAdapter = $this->dbAdapter;

    $select = new \Laminas\Db\Sql\Select('users');
    $select->where(['status' => 'active'])
           ->order('id DESC');

    $adapter = new \Laminas\Paginator\Adapter\DbSelect($select, $dbAdapter);
    $paginator = new \Laminas\Paginator\Paginator($adapter);

    $paginator->setCurrentPageNumber($page);
    $paginator->setItemCountPerPage($pageSize);

    return new \Laminas\View\Model\ViewModel([
        'users' => $paginator,
        'page'  => $page,
    ]);
}

Run this inside the web request context with read-only database credentials. The adapter issues a count query for the filtered select, then a paged select with LIMIT ? OFFSET ? for the current page. A missing where clause or an unbounded order can turn a cheap page into a full-table scan, so keep filters selective.

Offset pagination degrades on deep pages

Offset pagination is not free. When a user requests page 500, the database still reads and discards the rows before the offset. On large tables this costs time and I/O, and the cost grows linearly with page depth. For deep navigation—infinite scroll, admin exports, or API cursors—keyset pagination (the seek method) is usually preferable. Instead of OFFSET 10000, you filter on the last seen key: WHERE id < :lastId ORDER BY id DESC LIMIT 20. That keeps the query fast regardless of depth, though it requires a stable, indexed sort key.

Join-heavy selects can break the count

The adapter derives its count query from the same select. If your select joins tables in a way that multiplies rows—one-to-many relationships without DISTINCT or GROUP BY—the count query returns too many rows. The paginator then reports more pages than actually exist. Before shipping a joined query, inspect the generated count SQL. If row multiplication is possible, add DISTINCT to the columns or restructure the select so the count reflects unique parent rows. The same caution applies to GROUP BY clauses: the count query must aggregate correctly or the page count will be wrong.

Common mistakes and data-type traps

• Mutating the select after construction. The adapter captures the select object. Adding a where or changing the order after creating the adapter leads to pages that do not match the count.
• Mixing hydrators and raw arrays. Depending on how the result set is configured, rows may arrive as associative arrays or as hydrated objects. Passing the same result through a hydrator twice, or expecting array syntax on an object, produces unexpected data types in the view.
• Trusting raw page input. A negative or enormous page parameter can trigger an invalid offset. Clamp the value to a minimum of 1, and consider capping it against pageCount() before rendering.
• Assuming count() is cached. The count is a separate database query. Calling count() repeatedly inside a view loop can multiply database round-trips.

Practical verification steps

Use a dataset with a known size—1,000 rows is enough—and walk through the pages. Inspect the generated SQL for each page and confirm it contains LIMIT ? OFFSET ? with the expected bound values. Check that $paginator->count() returns the total row count and that $paginator->pageCount() equals ceil(total / pageSize). Navigate to the last page and verify the result set is not empty and that no extra rows are fetched beyond the dataset. If any of these checks fail, log the SQL and review the select construction before adjusting the adapter.