Database

Database: pagination

Split a long list into pages with one call on a query or a model, and show the page links in a view, an Inertia page or a Live component.

Introduction#

paginate() runs a query for one page of rows, reads the page number from the request's page parameter, and gives back a paginator: the rows of that page, with the page numbers and the URLs of the other pages.

Basic usage#

Paginating query builder results#

paginate() takes the number of rows per page:

import { DB, view } from '@orieljs/framework'import { Controller } from './Controller'export class UserController extends Controller {  /** Show all application users. */  async index() {    const users = await DB.table('users').paginate(15)    return view('user.index', { users })  }}

It runs two queries: one counts the rows, so the paginator knows the last page, and one reads the page.

A page number that isn't a whole number of 1 or more is page 1. A page past the last one is empty.

Simple pagination#

simplePaginate() reads one row more than a page, to tell whether there is a next page, and doesn't count the rows. Use it for long lists where you show only "Previous" and "Next":

const users = await DB.table('users').where('votes', '>', 100).simplePaginate(15)

Paginating model results#

Your models paginate in the same way, as a static method or at the end of a query. Without a number, a model's perPage, 15, is the page size:

const users = await User.paginate(15)const verified = await User.whereNotNull('email_verified_at').paginate(15)const posts = await user.posts().paginate(10)

Multiple paginator instances per page#

Two paginators on one page need their own parameter. The third argument names it, after the columns:

const users = await User.whereNotNull('email_verified_at').paginate(15, ['*'], 'users')

The fourth argument sets the page number yourself, in place of the request's: paginate(15, ['*'], 'page', 2).

Cursor pagination#

cursorPaginate() pages with a cursor, the values of the ordering columns of the last row, rather than with an offset. The query needs an orderBy(); a model's query orders by its key when it has none:

const users = await DB.table('users').orderBy('id').cursorPaginate(15)

The next page's URL carries the cursor in the cursor parameter, such as http://localhost/users?cursor=eyJpZCI6MTUsIl9wb2ludHNUb05leHRJdGVtcyI6dHJ1ZX0.

Cursor vs. offset pagination#

An offset query, limit 15 offset 15000, makes the database read and skip the rows before the page, which gets slow on large tables, and a row added while someone pages can show twice or be skipped. A cursor query, where id > 15000 limit 15, uses an index and is steady while rows change. In exchange, a cursor paginator has no page numbers and no total, only next and previous, and its ordering columns must be unique together. orderByRaw() and inRandomOrder() can't be used with it.

Manually creating a paginator#

To paginate rows you already have, make a LengthAwarePaginator (paginate()'s), a Paginator (simplePaginate()'s) or a CursorPaginator yourself, with the page's items, the total for a length-aware one, the page size and the page:

const paginator = new LengthAwarePaginator(pageOfItems, 100, 15, 2)

Customizing pagination URLs#

The links point at the current URL, without its query string. withPath() points them elsewhere:

const users = (await User.paginate(15)).withPath('/admin/users')

Appending query string values#

appends() adds values to every link, and withQueryString() keeps the current request's:

const users = (await User.paginate(15)).appends('sort', 'votes')const filtered = (await User.paginate(15)).withQueryString()

Appending hash fragments#

const users = (await User.paginate(15)).fragment('users')

Displaying pagination results#

A paginator is iterable, so for...of goes through its items, and items() returns them as an array. Its other methods describe the page:

In views#

Oriel's views are TSX, so there is no links() that prints the links for you. linkCollection() gives them as objects, with a url (null for the "..." gaps, and for Previous or Next when there is no such page), a label and whether the link is active, from "« Previous" to "Next »":

import type { LengthAwarePaginator } from '@orieljs/framework'interface User {  id: number  name: string}export default function Index({ users }: { users: LengthAwarePaginator<User> }) {  return (    <div>      <ul>        {users.items().map((user) => (          <li>{user.name}</li>        ))}      </ul>      <nav>        {users.linkCollection().map((link) =>          link.url === null ? <span>{link.label}</span> : <a href={link.url} class={link.active ? 'active' : undefined}>{link.label}</a>,        )}      </nav>    </div>  )}

onEachSide(count) sets how many page links show on each side of the current one, 3 unless you set it.

In Inertia pages#

Pass the paginator as a prop. It reaches the page as Laravel's JSON shape, which oriel inertia:types types: the data, the page numbers and URLs, and the links. Inertia.scroll(paginator) makes it a prop for infinite scrolling (see More Inertia features).

In Live components#

A Live component pages with the WithPagination mixin, and shows its links with the Pagination component:

import { Component, Pagination, WithPagination } from '@orieljs/framework'import { Post } from '../Models/Post'export default class ShowPosts extends WithPagination(Component) {  search = ''  async updatedSearch() {    await this.resetPage()  }  async render() {    const posts = await Post.whereLike('title', `%${this.search}%`).paginate(10)    return (      <div>        <ul>          {posts.items().map((post) => (            <li>{post.title}</li>          ))}        </ul>        <Pagination paginator={posts} />      </div>    )  }}

The page number is the component's page: its actions nextPage, previousPage, gotoPage(n) and resetPage move it. It isn't written to the address bar, so a reload shows page 1. Pagination takes paginate() and simplePaginate() paginators, not cursor ones.

Converting results to JSON#

A paginator returned from a route, or given to JSON.stringify(), becomes Laravel's JSON shape:

{  "current_page": 1,  "data": [{ "id": 1, "name": "Taylor" }],  "first_page_url": "http://localhost/users?page=1",  "from": 1,  "last_page": 4,  "last_page_url": "http://localhost/users?page=4",  "links": [{ "url": null, "label": "« Previous", "active": false }],  "next_page_url": "http://localhost/users?page=2",  "path": "http://localhost/users",  "per_page": 15,  "prev_page_url": null,  "to": 15,  "total": 50}

A simple paginator has no last_page, last_page_url, links or total, and a cursor paginator has next_cursor and prev_cursor in place of page numbers.