# How to Implement Pagination with Cache/Page Utilities in Gorig

> Learn to implement pagination with Gorig's cache/page utilities. Leverage the Pager T interface for efficient offset-based SQLite pagination with customizable queries and automatic LIMIT/OFFSET management.

- Repository: [Jom/gorig](https://github.com/jom-io/gorig)
- Tags: how-to-guide
- Published: 2026-03-05

---

**Gorig provides a generic `Pager[T]` interface that enables offset-based pagination with SQLite backing, allowing you to cache structs, query with dynamic conditions, and retrieve paginated results using the `Find()` method with automatic LIMIT/OFFSET handling.**

The `jom-io/gorig` repository offers a robust pagination layer built on top of its cache system. Located in the [`cache/page.go`](https://github.com/jom-io/gorig/blob/main/cache/page.go) and [`cache/page.sqlite.go`](https://github.com/jom-io/gorig/blob/main/cache/page.sqlite.go) files, these utilities allow developers to store arbitrary Go structs in SQLite, define indexes via struct tags, and execute paginated queries without writing raw SQL.

## Understanding the Gorig Pagination Architecture

### The Pager[T] Interface

At the heart of Gorig’s pagination system is the **`Pager[T]`** interface defined in [`cache/page.go`](https://github.com/jom-io/gorig/blob/main/cache/page.go). This generic interface provides CRUD-style methods including `Put()`, `Get()`, and `Delete()`, alongside powerful query helpers such as `Find()`, `GroupByTime()`, and `GroupByFields()`. The type parameter `T` represents the struct type you wish to cache and paginate.

### SQLite Implementation

The default concrete implementation is **`SQLiteCachePage[T]`** found in [`cache/page.sqlite.go`](https://github.com/jom-io/gorig/blob/main/cache/page.sqlite.go). This implementation automatically creates the necessary database table, generates indexes based on struct field tags, and constructs safe SQL queries from Go maps. When you call `NewPager[T]()` with the `cache.Sqlite` type, you receive a `Pager[T]` backed by this SQLite implementation.

## Setting Up Pagination with Cache/Page Utilities

To implement pagination, you must first define a concrete type with index tags, then instantiate a pager and populate it with data.

### Step 1: Define Your Model with Index Tags

Create a struct representing the data you want to cache. Use the **`idx`** struct tag to mark fields that should be indexed for querying:

```go
type User struct {
    ID   int    `json:"id" idx:"id"`
    Name string `json:"name" idx:"name"`
    Age  int    `json:"age" idx:"age"`
}

```

### Step 2: Instantiate the Pager

Use **`cache.NewPager[T]()`** to create a pager instance. Pass the context, cache type (`cache.Sqlite`), and an optional table name (defaults to the lowercase type name):

```go
func userPager(ctx context.Context) cache.Pager[User] {
    return cache.NewPager[User](ctx, cache.Sqlite, "user")
}

```

### Step 3: Populate the Cache

Insert items using the **`Put()`** method. This is typically done during application initialization or when syncing from an external data source:

```go
func seedUsers(ctx context.Context) error {
    p := userPager(ctx)
    users := []User{
        {ID: 1, Name: "Alice", Age: 30},
        {ID: 2, Name: "Bob", Age: 25},
        {ID: 3, Name: "Charlie", Age: 35},
    }
    for _, u := range users {
        if err := p.Put(u); err != nil {
            return err
        }
    }
    return nil
}

```

## Querying Paginated Data with Find()

The **`Find()`** method is the primary interface for retrieving paginated results. It automatically handles SQL generation, LIMIT/OFFSET calculation, and total count queries.

### Method Signature and Parameters

```go
func (p *SQLiteCachePage[T]) Find(page, size int, conditions map[string]any, sort ...PageSorter) (*PageCache[T], error)

```

- **`page`**: The page number (1-indexed).
- **`size`**: The number of items per page.
- **`conditions`**: A map defining WHERE clauses (supports operators like `$gt`, `$lt`, `$eq`).
- **`sort`**: Variadic `PageSorter` arguments controlling ORDER BY.

### Return Structure

The method returns a **`PageCache[T]`** struct containing:

```go
type PageCache[T any] struct {
    Total int   // Total number of matching records
    Page  int   // Current page number
    Size  int   // Items per page
    Items []T   // Slice of results for this page
}

```

### Practical Query Example

```go
func listUsers(ctx context.Context) (*cache.PageCache[User], error) {
    p := userPager(ctx)

    // Filter: users older than 20
    conditions := map[string]any{
        "age": map[string]any{
            "$gt": 20,
        },
    }

    // Sort by age descending
    sort := []cache.PageSorter{cache.PageSorterDesc("age")}

    // Get page 2, 10 items per page
    return p.Find(2, 10, conditions, sort...)
}

```

## Advanced Pagination Patterns

### Multi-Field Sorting

You can pass multiple **`PageSorter`** values to sort by secondary fields when primary values collide:

```go
sorts := []cache.PageSorter{
    cache.PageSorterAsc("name"),
    cache.PageSorterDesc("age"),
}
p.Find(1, 20, nil, sorts...)

```

### Grouped Pagination with GroupByFields

For aggregation queries, use **`GroupByFields()`** to paginate grouped results. This method accepts aggregation definitions via **`AggField`** structs:

```go
agg := []cache.AggField{
    {Field: "age", Agg: cache.AggAvg, Alias: "avg_age"},
}
groups, err := p.GroupByFields(
    nil, 
    []string{"name"}, 
    agg, 
    1, 5, 
    cache.PageSorterDesc("avg_age"),
)

```

### Time-Based Aggregation with GroupByTime

The **`GroupByTime()`** method buckets records by time intervals (minute, hour, day) and returns paginated aggregation results, useful for time-series data analysis.

## Summary

- **Gorig’s `cache/page` utilities** provide a generic `Pager[T]` interface for type-safe pagination backed by SQLite.
- **Key files**: [`cache/page.go`](https://github.com/jom-io/gorig/blob/main/cache/page.go) defines the interface and helpers; [`cache/page.sqlite.go`](https://github.com/jom-io/gorig/blob/main/cache/page.sqlite.go) implements SQL generation and query execution.
- **Core workflow**: Define indexed structs, instantiate with `NewPager()`, populate via `Put()`, and query using `Find()` with automatic LIMIT/OFFSET handling.
- **Advanced features**: Multi-field sorting with `PageSorter`, grouped aggregation via `GroupByFields()`, and time-based bucketing with `GroupByTime()`.

## Frequently Asked Questions

### How does Gorig handle SQL injection protection in the pagination queries?

The `SQLiteCachePage[T]` implementation in [`cache/page.sqlite.go`](https://github.com/jom-io/gorig/blob/main/cache/page.sqlite.go) constructs SQL clauses using parameterized queries and safe map-to-SQL conversion. When you pass conditions as `map[string]any` with operators like `$gt` or `$eq`, the implementation sanitizes these inputs and uses SQLite parameters rather than string concatenation, preventing SQL injection attacks.

### Can I use a different storage backend instead of SQLite for pagination?

While the default implementation uses SQLite via `SQLiteCachePage[T]`, the `Pager[T]` interface in [`cache/page.go`](https://github.com/jom-io/gorig/blob/main/cache/page.go) is storage-agnostic. You can implement the interface for other backends such as Redis, PostgreSQL, or in-memory stores by providing implementations of methods like `Put()`, `Get()`, `Find()`, and `Delete()`. The `NewPager()` function currently selects SQLite when passed `cache.Sqlite`, but you can extend this pattern for additional cache types.

### What is the performance impact of the automatic total count query in Find()?

The `Find()` method executes two queries: one to retrieve the paginated slice with LIMIT/OFFSET, and one to count total matching records for the `Total` field in `PageCache[T]`. For large datasets, the count query can become expensive because it requires scanning all matching rows. If you need to optimize for high-traffic scenarios, consider implementing cursor-based pagination or caching the total count separately, though this would require extending beyond the current `Pager[T]` interface implementation.

### How do I define composite indexes for complex query patterns?

The current implementation in [`cache/page.sqlite.go`](https://github.com/jom-io/gorig/blob/main/cache/page.sqlite.go) automatically creates indexes based on the `idx` struct tag on individual fields. For composite indexes spanning multiple columns, you would need to modify the table creation logic in the `SQLiteCachePage` initialization or execute custom SQL after table creation. The struct tag system currently supports single-field indexes, so composite indexing requires extending the `NewPager` initialization sequence to include additional `CREATE INDEX` statements for your specific query patterns.