Home › API › SIMPLE_SQL_PAGINATOR

SIMPLE_SQL_PAGINATOR

Cursor-based pagination builder for efficient traversal of large datasets. Unlike offset pagination, performance doesn't degrade as page numbers increase.

View Source

Cursor vs Offset Pagination

Offset (LIMIT 20 OFFSET 1000): SQLite scans and discards 1000 rows. Slow for large offsets.
Cursor (WHERE id > 1000 LIMIT 20): Uses index to jump directly. Constant time regardless of position.

Configuration

columns (a_columns: READABLE_STRING_8): like Current

Columns to select. Default is *.

where (a_condition: READABLE_STRING_8): like Current

Filter condition applied to all pages.

order_by (a_column: READABLE_STRING_8): like Current

Column to order and paginate by. Required. Default direction is ASC.

order_by_desc (a_column: READABLE_STRING_8): like Current

Order by column descending. Use for "newest first" pagination.

page_size (a_size: INTEGER): like Current

Number of items per page. Default is 20.

Navigation

first_page: SIMPLE_SQL_PAGE

Get the first page of results.

after (a_cursor: READABLE_STRING_8): SIMPLE_SQL_PAGE

Get page after the given cursor. Use next_cursor from previous page.

SIMPLE_SQL_PAGE

A single page of results with cursor information.

View Source

Access

items: SIMPLE_SQL_RESULT

The rows on this page.

next_cursor: detachable STRING_8

Cursor for the next page. Void if no more pages.

has_more: BOOLEAN

Are there more pages after this one?

count: INTEGER

Number of items on this page (may be less than page_size on last page).

Examples

Basic Pagination

local
    paginator: SIMPLE_SQL_PAGINATOR
    page: SIMPLE_SQL_PAGE
do
    paginator := db.paginator ("users")
        .columns ("id, name, email")
        .order_by ("id")
        .page_size (20)

    -- Get first page
    page := paginator.first_page
    across page.items as user loop
        print (user.string_value ("name") + "%N")
    end

    -- Get next page if available
    if page.has_more and attached page.next_cursor as cursor then
        page := paginator.after (cursor)
        -- Process next page...
    end
end

Iterate All Pages

local
    paginator: SIMPLE_SQL_PAGINATOR
    page: SIMPLE_SQL_PAGE
    cursor: detachable STRING_8
    page_num: INTEGER
do
    paginator := db.paginator ("documents")
        .where ("deleted_at IS NULL")
        .order_by ("id")
        .page_size (100)

    from
        page := paginator.first_page
        page_num := 1
    until
        not page.has_more
    loop
        print ("Page " + page_num.out + ": " + page.count.out + " items%N")

        -- Process page items...
        across page.items as doc loop
            -- ...
        end

        -- Get next page
        cursor := page.next_cursor
        if attached cursor then
            page := paginator.after (cursor)
            page_num := page_num + 1
        end
    end
end

Descending Order (Newest First)

paginator := db.paginator ("posts")
    .columns ("id, title, created_at")
    .order_by_desc ("created_at")
    .page_size (10)

page := paginator.first_page
-- Returns 10 most recent posts

With Filtering

paginator := db.paginator ("orders")
    .columns ("id, customer_id, total, status")
    .where ("status = 'pending' AND total > 100")
    .order_by ("id")
    .page_size (50)

Order Column Requirement

The order_by column must be unique or have a secondary unique column (like id) for stable pagination. Non-unique ordering may cause items to appear on multiple pages or be skipped.

Storing Cursor for Later

-- Save cursor to resume pagination later
if attached page.next_cursor as c then
    -- Store in session, URL parameter, etc.
    session.put ("documents_cursor", c)
end

-- Later: resume from stored cursor
if attached session.item ("documents_cursor") as stored_cursor then
    page := paginator.after (stored_cursor)
else
    page := paginator.first_page
end