Phalcon Pagination: Model vs QueryBuilder Adapter for Large Listings

Two adapters, two very different SQL statements

Phalcon is a PHP framework distributed as a compiled C extension, so the classes available to you depend on the extension build matching your PHP version. That matters here, because pagination is exposed as adapters under Phalcon\\Paginator\\Adapter, and the two that matter for a database-backed listing are Model and QueryBuilder. NativeArray exists for plain PHP arrays and is not a database strategy at all.

The distinction is not cosmetic. The Model adapter runs the model's find() with the parameters you supply and then slices the resulting Resultset in PHP — every matching row is materialised before one page is rendered. The QueryBuilder adapter takes a Phalcon\\Mvc\\Model\\Query\\Builder and pushes LIMIT/OFFSET into the generated SQL, so the database returns only the rows for the current page.

That is the decision in one sentence: convenience for small result sets, versus bounded memory and index-friendly slicing for large ones.

Adapter	What runs	Memory profile	Best fit
Model	find(), then slice in PHP	Grows with total matches	Small, bounded result sets
QueryBuilder	LIMIT/OFFSET in SQL	Bounded by page size	Large tables, indexed slices
NativeArray	Slice of an existing PHP array	Whatever the array already costs	Non-database data

A worked QueryBuilder example

The snippet below assumes Phalcon 5.x on a matching PHP build. Confirm your own version before adapting it — see the verification section. It paginates published posts joined to their category, ten per page. Run it inside a controller action or a service with access to the DI container; it uses the same database credentials as the rest of the model layer, so no extra permissions are involved.

use Phalcon\\Mvc\\Model\\Query\\Builder;
use Phalcon\\Paginator\\Adapter\\QueryBuilder as QueryBuilderPaginator;
use Phalcon\\Paginator\\Paginator;

$page = (int) $this->request->getQuery('page', 'int', 1);

$builder = new Builder();
$builder
    ->from(['p' => Posts::class])
    ->innerJoin(Categories::class, 'c.id = p.category_id', 'c')
    ->where('p.published = :published:', ['published' => 1])
    ->orderBy('p.published_at DESC, p.id DESC');

$adapter = new QueryBuilderPaginator([
    'builder' => $builder,
    'limit'   => 10,
    'page'    => $page,
]);

$result = (new Paginator($adapter))->paginate();

// $result->items, $result->total_items, $result->previous, $result->next

Two details in that snippet are load-bearing. First, orderBy ends with p.id DESC. Without a unique tiebreaker, rows sharing the same published_at value can be ordered differently between the count query and the page query, so a row can appear on two consecutive pages while another disappears entirely. Second, the page number is cast to an integer with a default of 1; passing 0 or a negative value is a boundary case worth handling explicitly rather than trusting the adapter to normalise it.

Phalcon 5 also offers a factory form that accepts an options array including an adapter key, while earlier majors commonly constructed the adapter object directly as shown. The two forms are not interchangeable across majors, so check the paginator documentation for your installed version before publishing code.

Reading the result object

paginate() returns a repository-style object, not an array. Views read named values: items, total_items, current, previous, next, first, last, limit. These property names have changed between Phalcon majors, so verify them against the docs for your version rather than copying an older tutorial.

Where total_items stops being trustworthy

The paginator derives a total count to build page links. That count is a separate query and does not always agree with what a user can actually page through:

• With GROUP BY or DISTINCT, the count reflects grouped rows while the page query may return a different shape.
• With joins that multiply rows — one post to many tags, for example — the count can exceed the number of distinct entities you intend to display.
• With a filter matching nothing, confirm that total_items is 0 and that last does not point at a phantom page.

Treat total_items as a navigation hint, not an exact figure, unless you have asserted it against a direct COUNT(*) of the same filtered query.

Limitations and how to check the result

Two things this choice does not fix. Large OFFSET values are slow on big tables with either adapter, because the database still walks past the skipped rows; keyset (seek) pagination is a separate design decision, not a paginator configuration option. And no adapter makes a missing index fast. Avoid assuming universal memory or speed figures — row width, schema, indexes, and data volume all change the outcome.

To confirm which SQL actually runs, enable the database profiler or SQL logging and compare both adapters on the same table. The QueryBuilder adapter should show LIMIT/OFFSET; the Model adapter should not. Print the installed version from the shell with php --ri phalcon, then read the paginator documentation for that major.

A short test suite covers most of the risk:

1. Assert total_items equals a direct COUNT(*) of the same filtered query.
2. Assert consecutive pages share no primary keys.
3. Exercise page 0, a page past the last one, and a filter matching zero rows.
4. Compare memory_get_peak_usage() between the two adapters on a table with many rows.

If the listing is genuinely small — a few hundred rows, or a result set already bounded by a tight filter — the Model adapter is the shorter path and reuses the find() parameter arrays you already have. Once the table grows past the point where materialising every match is acceptable, switch to QueryBuilder and add the unique tiebreaker to ORDER BY in the same change.