# Queries and pagination

> Query models with GORM, sort by a client-chosen column safely with lagoon.OrderBy, and return Laravel-shaped pages with lagoon.Paginate.

Queries are plain GORM: `Where`, `Joins`, `Preload`, `Count`, `Find` and the rest work as the [GORM documentation](https://gorm.io/docs/) describes. [lagoon](/docs/api/lagoon.md) adds two helpers for list endpoints, which almost always let the client choose the sort order and ask for one page.

Pass the request context to every query with `WithContext`, so a cancelled request stops its query.

## Sorting by a column the client names

A list endpoint usually takes `?sort=title&order=desc`. Never pass those values to `Order` directly: a column name is SQL, not a bound parameter. `lagoon.OrderBy` appends the ORDER BY only when the column is in an allow-list you give it and the direction is `asc` or `desc`, and returns an error for anything else:

```go
// A dry-run handle shows the SQL without a database.
db, _ := gorm.Open(postgres.New(postgres.Config{DSN: "host=127.0.0.1"}), &gorm.Config{DryRun: true, DisableAutomaticPing: true})
allowed := []string{"title", "views"}

q, err := lagoon.OrderBy(db.Model(&Post{}), "views", "desc", allowed)
if err != nil {
	fmt.Println(err)
	return
}
var posts []Post
fmt.Println(q.Find(&posts).Statement.SQL.String())

_, err = lagoon.OrderBy(db, "api_token", "asc", allowed)
fmt.Println(err)
_, err = lagoon.OrderBy(db, "title", "asc; DROP TABLE acme_blog_posts", allowed)
fmt.Println(err)
// Output:
// SELECT * FROM "acme_blog_posts" WHERE "acme_blog_posts"."deleted_at" IS NULL ORDER BY views DESC
// lagoon: order column "api_token" is not allow-listed
// lagoon: order direction "asc; DROP TABLE acme_blog_posts" is not allow-listed
```

Answer the error as a validation failure (422). The column must match an allow-list entry exactly, so list the qualified name (`acme_blog_posts.title`) when the query joins another table.

## Sorting with a collation

Text sorts by the database's default collation unless you pass `lagoon.Collate`. A list that must follow one language's alphabet (Polish puts Ł between L and M, for example) passes `lagoon.Collate("pl-x-icu")`, and `lagoon.OrderBy` adds a `COLLATE` clause for that column only. ICU collations named `<language>-x-icu` exist in any PostgreSQL built with ICU support, which includes the official Docker images, so the database needs no special locale:

```go
// A dry-run handle shows the SQL without a database.
db, _ := gorm.Open(postgres.New(postgres.Config{DSN: "host=127.0.0.1"}), &gorm.Config{DryRun: true, DisableAutomaticPing: true})
allowed := []string{"title", "views"}

// Polish alphabetical order, whatever the database's default locale.
q, err := lagoon.OrderBy(db.Model(&Post{}), "title", "asc", allowed, lagoon.Collate("pl-x-icu"))
if err != nil {
	fmt.Println(err)
	return
}
var posts []Post
fmt.Println(q.Find(&posts).Statement.SQL.String())

_, err = lagoon.OrderBy(db, "title", "asc", allowed, lagoon.Collate(`pl-x-icu" ASC, (SELECT 1) --`))
fmt.Println(err)
// Output:
// SELECT * FROM "acme_blog_posts" WHERE "acme_blog_posts"."deleted_at" IS NULL ORDER BY title COLLATE "pl-x-icu" ASC
// lagoon: order collation "pl-x-icu\" ASC, (SELECT 1) --" is not a valid collation name
```

The collation name is validated (ASCII letters, digits, `_`, `-`, `.` and `@`, at most 63 bytes) and quoted as an identifier; anything else is an error, and no SQL is built. An index only helps that ORDER BY when it is built with the same collation.

## Pagination

`lagoon.Paginate` wraps the rows of one page in the envelope Laravel's paginator produces for the API: `data` and a `meta` object with `current_page`, `last_page`, `per_page` and `total`. You run the count and the page query yourself, so the query stays under your control:

```go
q, err := lagoon.OrderBy(db.WithContext(ctx).Model(&Post{}), sort, dir, []string{"title", "views"})
if err != nil {
	return lagoon.Page[Post]{}, err // answer 422: the client asked for a column it may not sort by
}
var total int64
if err := q.Count(&total).Error; err != nil {
	return lagoon.Page[Post]{}, err
}
var posts []Post
if err := q.Offset((page - 1) * perPage).Limit(perPage).Find(&posts).Error; err != nil {
	return lagoon.Page[Post]{}, err
}
return lagoon.Paginate(posts, page, perPage, total), nil
```

The result is a `lagoon.Page` whose `lagoon.PageMeta` marshals to the Laravel field names:

```go
rows := []map[string]any{{"id": 3, "title": "Third"}}
page := lagoon.Paginate(rows, 2, 2, 3)
out, _ := json.Marshal(page)
fmt.Println(string(out))

empty, _ := json.Marshal(lagoon.Paginate[map[string]any](nil, 1, 15, 0))
fmt.Println(string(empty))
// Output:
// {"data":[{"id":3,"title":"Third"}],"meta":{"current_page":2,"last_page":2,"per_page":2,"total":3}}
// {"data":[],"meta":{"current_page":1,"last_page":1,"per_page":15,"total":0}}
```

A nil slice becomes `[]`, and a zero or negative page size gives one page instead of dividing by zero. There is no `links` block. When a ported endpoint's response has a different shape, build that shape yourself: the existing clients define the contract.

Clamp `page` and `per_page` from the request before you use them, for example to at least 1 and at most 100, so a client cannot ask for the whole table in one page.