Paginate results

The pagination API lets you split the results of a query into chunks containing a maximum number of records.

Number-based pagination

Number-based pagination splits the results into a fixed number of pages:

// Create a result-set
res = sess.Collection("posts").Find()

// Set the amount of records by chunk
p := res.Paginate(20)

// Get first chunk of results (page 1)
err = p.All(&posts)

// Get second chunk of results (limit 20, offset 40)
err = p.Page(2).All(&posts)

If you’re working with the SQL builder, use SelectFrom instead of Collection:

q = sess.SQL().SelectFrom("posts").Paginate(20)

Cursor-based pagination

If number-based pagination does not fit your case, you can also set the record where you want to begin and the results you want to fetch thereon:

res = sess.Collection("posts").
  Find().
  Paginate(20).
  Cursor("id")

err = res.All(&posts)

// Get the results that follow the last record of the previous
// query in groups of 20.
res = res.NextPage(posts[len(posts)-1].ID)

// Get the first 20 results (limit 20, offset 20)
err = res.All(&posts)

Pagination API tools

To know the total number of entries and pages into which the result set was divided, you can use:

res = res.Paginate(23)

totalNumberOfEntries, err = res.TotalEntries()
...

totalNumberOfPages, err = res.TotalPages()
...

< back
8/18
next >

              package main

import (
	"fmt"
	"log"

	"github.com/upper/db/v4/adapter/cockroachdb"
)

var settings = cockroachdb.ConnectionURL{
	Database: `booktown`,
	Host:     `cockroachdb.demo.upper.io`,
	User:     `demouser`,
	Password: `demop4ss`,
}

type Customer struct {
	ID        uint   `db:"id"`
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}

func main() {
	sess, err := cockroachdb.Open(settings)
	if err != nil {
		log.Fatal(err)
	}
	defer sess.Close()

	customersCol := sess.Collection("customers")

	// Create a paginator and sets 10 records by page.
	res := customersCol.Find().
		OrderBy("last_name", "first_name")

	p := res.Paginate(10)

	// Try changing the page number and running the example
	const pageNumber = 2

	// Copy all the records from the current page into the customers slice.
	var customers []Customer
	err = p.Page(pageNumber).All(&customers)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("List of costumers (page %d):\n", pageNumber)
	for i, customer := range customers {
		fmt.Printf("%d: %q, %q\n", i, customer.LastName, customer.FirstName)
	}

	totalNumberOfEntries, err := p.TotalEntries()
	if err != nil {
		log.Fatal("p.TotalEntries: ", err)
	}

	totalNumberOfPages, err := p.TotalPages()
	if err != nil {
		log.Fatal("p.TotalPages: ", err)
	}

	fmt.Println("")
	fmt.Printf("Total entries: %d. Total pages: %d", totalNumberOfEntries, totalNumberOfPages)
}

            