Pagination

Recipe: Implement efficient pagination for large datasets.

Soy supports both offset-based and cursor-based pagination. Choose based on your requirements.

Offset-Based Pagination

Simple to implement, works with any ordering:

func GetUsersPage(ctx context.Context, page, perPage int) ([]*User, error) {
    offset := (page - 1) * perPage

    return users.Query().
        OrderBy("created_at", "desc").
        Limit(perPage).
        Offset(offset).
        Exec(ctx, nil)
}

With Total Count

type PagedResult[T any] struct {
    Items      []*T
    Total      int
    Page       int
    PerPage    int
    TotalPages int
}

func GetUsersPagedResult(ctx context.Context, page, perPage int) (*PagedResult[User], error) {
    offset := (page - 1) * perPage

    // Get total count
    total, err := users.Count().Exec(ctx, nil)
    if err != nil {
        return nil, err
    }

    // Get page items
    items, err := users.Query().
        OrderBy("created_at", "desc").
        Limit(perPage).
        Offset(offset).
        Exec(ctx, nil)
    if err != nil {
        return nil, err
    }

    totalPages := int(math.Ceil(float64(total) / float64(perPage)))

    return &PagedResult[User]{
        Items:      items,
        Total:      int(total),
        Page:       page,
        PerPage:    perPage,
        TotalPages: totalPages,
    }, nil
}

Limitations

Offset pagination has performance issues at scale:

• Database scans all rows up to offset
• Page N requires scanning N * perPage rows
• Inconsistent results if data changes between pages

Cursor-Based Pagination

More efficient for large datasets. Uses the last item's values to fetch the next page.

By ID

func GetUsersAfter(ctx context.Context, afterID, limit int) ([]*User, error) {
    if afterID == 0 {
        // First page
        return users.Query().
            OrderBy("id", "asc").
            Limit(limit).
            Exec(ctx, nil)
    }

    return users.Query().
        Where("id", ">", "after_id").
        OrderBy("id", "asc").
        Limit(limit).
        Exec(ctx, map[string]any{"after_id": afterID})
}

By Timestamp

func GetUsersBefore(ctx context.Context, beforeTime time.Time, limit int) ([]*User, error) {
    if beforeTime.IsZero() {
        // First page (newest first)
        return users.Query().
            OrderBy("created_at", "desc").
            Limit(limit).
            Exec(ctx, nil)
    }

    return users.Query().
        Where("created_at", "<", "before_time").
        OrderBy("created_at", "desc").
        Limit(limit).
        Exec(ctx, map[string]any{"before_time": beforeTime})
}

Compound Cursor

For non-unique sort columns, use a compound cursor. This requires nested conditions which are best expressed using the spec API:

type Cursor struct {
    Score     int
    ID        int
}

func GetUsersByScore(ctx context.Context, cursor *Cursor, limit int) ([]*User, error) {
    if cursor == nil {
        // First page
        return users.Query().
            OrderBy("score", "desc").
            OrderBy("id", "desc").
            Limit(limit).
            Exec(ctx, nil)
    }

    // Next page: score < cursor.score OR (score = cursor.score AND id < cursor.id)
    // Use spec API for nested AND/OR conditions
    spec := soy.QuerySpec{
        Where: []soy.ConditionSpec{
            {
                Logic: "OR",
                Group: []soy.ConditionSpec{
                    {Field: "score", Operator: "<", Param: "cursor_score"},
                    {
                        Logic: "AND",
                        Group: []soy.ConditionSpec{
                            {Field: "score", Operator: "=", Param: "cursor_score"},
                            {Field: "id", Operator: "<", Param: "cursor_id"},
                        },
                    },
                },
            },
        },
        OrderBy: []soy.OrderBySpec{
            {Field: "score", Direction: "desc"},
            {Field: "id", Direction: "desc"},
        },
        Limit: &limit,
    }

    return users.QueryFromSpec(spec).Exec(ctx, map[string]any{
        "cursor_score": cursor.Score,
        "cursor_id":    cursor.ID,
    })
}

Opaque Cursors

Encode cursor data for API responses:

import "encoding/base64"

type CursorData struct {
    ID        int       `json:"id"`
    CreatedAt time.Time `json:"created_at"`
}

func EncodeCursor(user *User) string {
    data := CursorData{ID: user.ID, CreatedAt: user.CreatedAt}
    bytes, _ := json.Marshal(data)
    return base64.StdEncoding.EncodeToString(bytes)
}

func DecodeCursor(cursor string) (*CursorData, error) {
    bytes, err := base64.StdEncoding.DecodeString(cursor)
    if err != nil {
        return nil, err
    }
    var data CursorData
    if err := json.Unmarshal(bytes, &data); err != nil {
        return nil, err
    }
    return &data, nil
}

Paginated Response

Standard response format:

type Connection[T any] struct {
    Edges    []Edge[T] `json:"edges"`
    PageInfo PageInfo  `json:"pageInfo"`
}

type Edge[T any] struct {
    Node   *T     `json:"node"`
    Cursor string `json:"cursor"`
}

type PageInfo struct {
    HasNextPage     bool   `json:"hasNextPage"`
    HasPreviousPage bool   `json:"hasPreviousPage"`
    StartCursor     string `json:"startCursor,omitempty"`
    EndCursor       string `json:"endCursor,omitempty"`
}

func GetUsersConnection(ctx context.Context, after string, first int) (*Connection[User], error) {
    // Fetch one extra to determine hasNextPage
    limit := first + 1

    var items []*User
    var err error

    if after == "" {
        items, err = users.Query().
            OrderBy("id", "asc").
            Limit(limit).
            Exec(ctx, nil)
    } else {
        cursor, err := DecodeCursor(after)
        if err != nil {
            return nil, err
        }
        items, err = users.Query().
            Where("id", ">", "after_id").
            OrderBy("id", "asc").
            Limit(limit).
            Exec(ctx, map[string]any{"after_id": cursor.ID})
    }
    if err != nil {
        return nil, err
    }

    hasNextPage := len(items) > first
    if hasNextPage {
        items = items[:first] // Remove extra item
    }

    edges := make([]Edge[User], len(items))
    for i, item := range items {
        edges[i] = Edge[User]{
            Node:   item,
            Cursor: EncodeCursor(item),
        }
    }

    var startCursor, endCursor string
    if len(edges) > 0 {
        startCursor = edges[0].Cursor
        endCursor = edges[len(edges)-1].Cursor
    }

    return &Connection[User]{
        Edges: edges,
        PageInfo: PageInfo{
            HasNextPage:     hasNextPage,
            HasPreviousPage: after != "",
            StartCursor:     startCursor,
            EndCursor:       endCursor,
        },
    }, nil
}

Choosing a Strategy

Use offset pagination for:

• Small datasets (< 10,000 rows)
• Admin interfaces needing "jump to page"
• Simple implementations

Use cursor pagination for:

• Large datasets
• Real-time feeds
• API endpoints with high traffic
• Data that changes frequently