Paginating Phalcon ORM Results Without Loading the Whole Table

Your controller fetches every matching row, slices the array in PHP, and renders ten items. It works fine at 200 rows and falls over at 200,000. The fix is not hand-rolled LIMIT/OFFSET SQL — it's Phalcon's built-in Paginator component, which generates the COUNT and LIMIT queries for you and hands back page metadata (total pages, next, previous) without ever materializing the full result set.

The takeaway: for ORM-backed lists, use Phalcon\Paginator\Adapter\QueryBuilder and stop writing pagination math by hand. You'll get accurate navigation metadata and keep working with hydrated model instances.

Why the QueryBuilder adapter and not raw SQL

Phalcon ships several paginator adapters (arrays, native arrays, models, query builder). The QueryBuilder adapter is the right default for ORM data because it works from a Phalcon\Mvc\Model\Query\Builder rather than a SQL string. That means you keep parameter binding, model hydration, and joins for eager-loading related records — the paginated items come back as real model objects you can pass to views, serializers, or ACL checks.

Internally the adapter derives a COUNT query from your builder and then runs the real query with LIMIT/OFFSET. So each page costs exactly two queries, and memory usage stays flat regardless of table size. Compare that with fetching everything and calling array_slice(): simple to write, catastrophic at scale.

A worked example: paginating an orders list

Assume Phalcon 4/5 with the ORM wired up, and an Orders model. In a controller action:

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

public function listAction()
{
    // Never trust the incoming page number: clamp to a positive int.
    $page = max(1, (int) $this->request->getQuery('page', 'int', 1));

    $builder = new Builder();
    $builder->from(['o' => Orders::class])
            ->join('Customers', 'c.id = o.customer_id', 'c')
            ->where('o.status = :status:', ['status' => 'open'])
            ->orderBy('o.created_at DESC');

    $paginator = new PaginatorQueryBuilder([
        'builder' => $builder,
        'limit'   => 20,
        'page'    => $page,
    ]);

    $page = $paginator->paginate();

    $this->view->setVars([
        'orders' => $page->items,       // hydrated Orders models
        'total'  => $page->total_items,
        'pages'  => $page->total_pages,
        'next'   => $page->next,
        'prev'   => $page->previous,
        'last'   => $page->last,
    ]);
}

The page object is a plain stdClass-like value, not a collection type. Views iterate $page->items and render navigation from next/previous/last. Because that navigation markup is identical across every list in your app, wrap it in a view helper once — duplicating "if current < last show Next" logic in every template is how off-by-one bugs breed.

Verify it actually issues two queries

Don't take the efficiency claim on faith. Attach a database listener (or enable PDO-level query logging in dev) and confirm that requesting a page produces exactly one COUNT(...) query and one SELECT ... LIMIT 20 OFFSET n. If you see N+1 queries instead, it's coming from lazy-loaded relations in your views, not the paginator — fix that with joins in the builder.

A cheap integration test: seed 50 rows, request page 2 with limit 10, and assert count($page->items) === 10, $page->total_items === 50, $page->next === 3, and $page->previous === 1. That one test catches most wiring mistakes, including a misconfigured DI service for the models manager.

Trade-offs you should know before shipping

Deep pages get slow. Offset pagination makes the database scan and discard every skipped row. Page 5,000 of a million-row table is expensive no matter what framework you use. If you genuinely need deep traversal (infinite scroll over huge datasets), keyset/cursor pagination (WHERE id < :last_seen ORDER BY id DESC LIMIT 20) is dramatically faster — but Phalcon's built-in adapters don't provide it, so you'd implement it manually with the query builder.

The COUNT can be the bottleneck. On builders with heavy joins or GROUP BY, the derived count query may cost more than the page fetch itself. For expensive filtered lists, consider caching the count keyed by the filter hash, or simplifying the count query.

Version drift is real. Phalcon is a C extension, and namespaces and constructor signatures have shifted across major versions (v3 vs v4/v5). Before copying any example — including this one — check Phalcon\Version::get() and confirm the adapter class name against your installed version's stubs.

Closing

Reach for the QueryBuilder paginator adapter whenever a list is backed by the ORM and users browse it page by page. Clamp the page parameter, verify the two-query behavior with a listener, and cover the metadata with one small test. If profiling later shows deep-page pain, that's your signal to hand-roll keyset pagination for that one hot endpoint — not to abandon the paginator everywhere else.