Ordering and paging

Ordering takes field probes, so a request can never smuggle in a column name. For large results there are three ways through, and each suits a different job.

Ordering

users, err := db.Users.
	OrderBy(&U.Age).
	ThenByDesc(&U.Name).
	All(ctx)

OrderByfunc (t *Table[T]) OrderBy[V Ordered](field *V) *Query[T]OrderBy starts a query ordered by field, ascending. and OrderByDescfunc (t *Table[T]) OrderByDesc[V Ordered]( field *V, ) *Query[T]OrderByDesc starts a query ordered by field, descending. start an ordering. ThenByfunc (q *Query[T]) ThenBy[V Ordered](field *V) *Query[T]ThenBy adds an ascending ordering after OrderBy. and ThenByDescfunc (q *Query[T]) ThenByDesc[V Ordered](field *V) *Query[T]ThenByDesc adds a descending ordering after OrderBy. continue one, and without one they return ant.ErrThenWithoutOrdervar ErrThenWithoutOrder = errors.New("ant: ThenBy needs a preceding OrderBy")ErrThenWithoutOrder reports ThenBy on a query with no OrderBy..

For expressions and NULL placement, Orderfunc (t *Table[T]) Order(terms ...Sort) *Query[T]Order starts a query ordered by terms from Asc and Desc. takes terms directly.

users, err := db.Users.
	Order(
		ant.Col(&U.Bio).Asc().NullsLast(),
		ant.Lower(&U.Name).Desc(),
	).
	All(ctx)

And a few shortcuts:

newest, err := db.Users.Query().Latest(&U.CreatedAt).First(ctx)
oldest, err := db.Users.Query().Oldest(&U.CreatedAt).First(ctx)
shuffled, err := db.Users.Query().OrderByRandom().All(ctx)
plain, err := db.Users.OrderBy(&U.Name).ClearOrder().All(ctx)

Limit and offset

page, err := db.Users.OrderBy(&U.Name).Limit(10).Offset(20).All(ctx)

Numbered pages

Page gives you the rows plus the totals you need for paging links. Pages count from 1, and past the end you get empty Items with the right Total.

page, err := db.Users.OrderBy(&U.Name).Page(ctx, 2, 10)
if err != nil {
	return err
}

fmt.Println(page.Items)     // this page's rows
fmt.Println(page.Total)     // rows in all
fmt.Println(page.Pages())   // pages in all
fmt.Println(page.HasNext()) // is there another?

Order the query, or pages may overlap. On large tables, counting and skipping rows gets slow, and that's what a cursor is for.

Cursors

After pages by position in the ordering. Every page costs the same however deep you go, and rows added in the meantime don't shift anything. Hand Next to the client, and take it back as the cursor.

query := db.Users.OrderBy(&U.Name)

page, err := query.After(ctx, "", 20) // "" is the first page
if err != nil {
	return err
}

next, err := query.After(ctx, page.Next, 20)

Next is empty on the last page. The key breaks ties, so no row is lost at a page boundary, and with no ordering at all it pages by key.

The ordering has to use plain columns of this table that are never NULL, and ant refuses anything else rather than silently skip rows. A cursor that's been tampered with is ant.ErrCursorvar ErrCursor = errors.New("ant: invalid cursor")ErrCursor reports a cursor that this query cannot use..

Chunks

Chunkfunc (q *Query[T]) Chunk(ctx context.Context, size int, f func([]*T) error) errorChunk reads matching rows in groups of size, calling f with each until f returns an error or the rows run out. reads a large result in groups, holding only one group in memory at a time. Includefunc (t *Table[T]) Include[R any](rel *[]R, shape ...func(*Query[R]) *Query[R]) *Query[T]Include starts a query that loads a one-to-many relation. works per group, and returning an error stops it.

err := db.Users.OrderBy(&U.Name).Chunk(ctx, 500, func(users []*User) error {
	return send(users)
})

Changing rows while you read

Chunk pages by offset, so deleting rows in the callback makes it skip others. ChunkByKeyfunc (q *Query[T]) ChunkByKey(ctx context.Context, size int, f func([]*T) error) errorChunkByKey reads matching rows in key order, in groups of size. keeps its place by key instead, in key order, so the callback can change or delete whatever it reads.

err := db.Users.Where(ant.Gt(&U.Age, 100)).ChunkByKey(ctx, 500, func(users []*User) error {
	for _, user := range users {
		if err := db.Users.DeleteByID(ctx, user.ID); err != nil {
			return err
		}
	}

	return nil
})

Which one

You need	Use
Page numbers in a small table	Page
An infinite scroll or a public API	After
To process everything in batches	Chunk, or ChunkByKey if you change rows
To stream without batches	Each

See it tested: tests/features/performance