SuperAPI AI Directory

pagination-filtering

Coding & development

View repository

Updated 10-16 09:31

About this skill

Pagination, Filtering, Sorting, Search

References:Stripe API Pagination、GitHub REST API

Use Cases

  • All list endpoints
  • Queries with large data volumes
  • Complex filtering scenarios
  • Full-text search

Pagination Methods

1. Offset Pagination (Page-number Pagination)

GET /users?page=1&page_size=20
GET /users?offset=0&limit=20

Response: { "data": [...], "pagination": { "page": 1, "page_size": 20, "total": 156, "total_pages": 8 } }

Applicable: ✅ Small data volume (< 100,000) ✅ Need page jumping (user goes to page 5) ✅ Need total (display "156 total")

Not applicable: ❌ Large data volume (poor performance, deep pagination OFFSET is slow) ❌ High-concurrency updates (data may be misaligned)


### 2. Cursor 分页(游标分页)

```text
GET /users?cursor=usr_abc123&limit=20

响应:
{
  "data": [...],
  "pagination": {
    "next_cursor": "usr_xyz789",
    "has_more": true
  }
}

适用:
  ✅ 数据量大(无穷滚动)
  ✅ 实时数据流
  ✅ 性能要求高
  ✅ 数据频繁更新

不适用:
  ❌ 需要跳页
  ❌ 需要精确 total

实现:
  cursor 通常是排序字段的值(id / created_at)
  WHERE id > cursor ORDER BY id LIMIT 20

3. Time-based Pagination

GET /events?since=2026-01-15T00:00:00Z&until=2026-01-16T00:00:00Z

适用:日志、事件流、时间相关数据

Selection Decision

后台管理 / 小数据 → offset
公开 API / 大数据 / 信息流 → cursor
日志 / 事件 → time-based

Filtering

Basic Filtering

GET /users?status=active
GET /users?role=admin
GET /users?created_after=2026-01-01

Multi-value Filtering

GET /users?status=active,inactive
GET /users?role=admin&role=manager   (重复参数)

Range Filtering

GET /products?price_min=10&price_max=100
GET /orders?created_after=2026-01-01&created_before=2026-01-31

Operator Filtering (Advanced)

GET /products?price[gte]=10&price[lte]=100
GET /products?stock[gt]=0
GET /products?name[like]=phone

操作符:
  eq  - 等于
  ne  - 不等于
  gt  - 大于
  gte - 大于等于
  lt  - 小于
  lte - 小于等于
  in  - 在列表中
  nin - 不在列表中
  like - 模糊匹配

Full-text Search

GET /products?q=iphone
GET /products?keyword=iphone

参数名:q / keyword / search
搜索范围:通常服务端定义(如 name + description)

Sorting

GET /users?sort=created_at        升序(默认)
GET /users?sort=-created_at       降序(- 前缀)
GET /users?sort=-priority,name    多字段排序

或:
GET /users?sort_by=created_at&order=desc

Sparse Fieldsets

GET /users/{id}?fields=id,name,email
返回:只包含这三个字段

GET /orders/{id}?include=items,user
返回:默认字段 + 嵌套关联

适用:
  - 移动端节省流量
  - 性能优化
  - 减少敏感字段返回

Unified Convention Template

## 列表接口规范

### 分页参数(offset 风格)
- page: integer, default=1, min=1
- page_size: integer, default=20, min=1, max=100

### 分页响应
{
  "data": [...],
  "pagination": {
    "page": 1,
    "page_size": 20,
    "total": 156,
    "total_pages": 8,
    "has_next": true,
    "has_previous": false
  }
}

### 筛选参数
- 单值:?status=active
- 多值:?status=active,inactive
- 范围:?created_after=...&created_before=...

### 排序
- 单字段:?sort=created_at(升序)
- 单字段降序:?sort=-created_at
- 多字段:?sort=-priority,name

### 搜索
- 全文:?q=keyword

### 字段选择
- 包含:?fields=id,name,email
- 关联:?include=user,items

Complete Example

GET /products?
  q=phone&
  category=electronics&
  price_min=100&
  price_max=1000&
  in_stock=true&
  sort=-rating,-created_at&
  page=1&
  page_size=20&
  fields=id,name,price,rating

→
{
  "data": [
    {
      "id": "prd_001",
      "name": "iPhone 15",
      "price": 999,
      "rating": 4.8
    }
  ],
  "pagination": {
    "page": 1,
    "page_size": 20,
    "total": 87,
    "total_pages": 5,
    "has_next": true,
    "has_previous": false
  }
}

Workflow

1. 评估数据量和查询场景
2. 选择分页方式(offset / cursor / time)
3. 列出可筛选字段
4. 决定支持的操作符(基础 / 高级)
5. 列出可排序字段和默认排序
6. 决定是否支持搜索
7. 决定是否支持字段选择
8. 写入 OpenAPI 参数定义
9. 输出列表接口规范

Quality Self-check

□ 全 API 用同一种分页方式(不要混用)
□ page_size 有上限(防止滥用,max 100)
□ 排序字段命名一致(- 前缀降序)
□ 筛选参数有校验(防注入)
□ 搜索的 q 参数有最小长度
□ 列表响应有 total 或 has_more
□ 大数据集用 cursor 不用 offset
□ 默认排序明确(不能不排序)

Common Pitfalls

  1. Different pagination styles for different endpoints——/users uses page, /orders uses cursor
  2. No upper limit on page_size——attacker requests page_size=10000
  3. OFFSET deep pagination is slow——OFFSET 100000 LIMIT 20 has poor performance
  4. Sort fields not restricted——?sort=password_hash exposes fields
  5. total not returned——frontend cannot display "X total"
  6. Filters directly concatenated into SQL——SQL injection risk
  7. Search causes full table scan——no index, crashes when data volume is large
  8. Inconsistent response formats——some use data, some use items, some use results

Supporting Templates

  • templates/pagination-filter-sort-template.md — Pagination parameters + filtering parameters + list response structure template

Collaboration with Other Skills

上游:
  endpoint-design → 列表端点清单

平行:
  request-response → 列表响应结构

下游:
  openapi-mock → OpenAPI 参数定义
  转交后端 → 数据库索引设计(database-engineer 协作)