GORM Keyset Pagination PostgreSQL

// PostgreSQL keyset pagination with gorm
package main

import (
	"fmt"
	"log"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

type Invoice struct {
	ID        uint   `gorm:"primaryKey"`
	Customer  string `gorm:"index:idx_customer_created,priority:1"`
	CreatedAt int64  `gorm:"index:idx_customer_created,priority:2;autoCreateTime:false"`
	Amount    int
}

type Page struct {
	Items  []Invoice
	NextID uint
	NextAt int64
	HasNext bool
}

func keysetPage(db *gorm.DB, customer string, limit int,
	afterID uint, afterAt int64,
) (Page, error) {
	query := db.Where("customer = ?", customer).
		Order("created_at DESC, id DESC")

	if afterID != 0 {
		// 复合键条件：严格晚于锚点 (created_at, id)
		query = query.Where(
			"(created_at < ?) OR (created_at = ? AND id < ?)",
			afterAt, afterAt, afterID,
		)
	}

	rows := make([]Invoice, 0, limit+1)
	if err := query.Limit(limit + 1).Find(&rows).Error; err != nil {
		return Page{}, fmt.Errorf("keyset query: %w", err)
	}

	hasNext := len(rows) > limit
	if hasNext {
		rows = rows[:limit]
	}

	page := Page{Items: rows, HasNext: hasNext}
	if len(rows) > 0 {
		last := rows[len(rows)-1]
		page.NextID = last.ID
		page.NextAt = last.CreatedAt
	}
	return page, nil
}

func main() {
	db, err := gorm.Open(postgres.Open(
		"host=localhost user=app dbname=billing"),
		&gorm.Config{Logger: logger.Discard},
	)
	if err != nil {
		log.Fatal(err)
	}

	page, err := keysetPage(db, "acme", 20, 0, 0)
	if err != nil {
		log.Fatal(err)
	}

	next, err := keysetPage(db, "acme", 20, page.NextID, page.NextAt)
	fmt.Println(len(page.Items), page.HasNext, len(next.Items), next.HasNext, err)
}