使用Postgres与sqlx在事务中复用过滤数据执行多查询

针对你的需求，这里提供两种高效且易实现的方案，都能避免重复过滤，同时解决结构体映射和临时表的问题：

方案一：WITH 语句 + 多结果集扫描 #

PostgreSQL 的 WITH 子句会将过滤结果缓存（只读场景下会被优化为单次扫描），你可以在同一个 SQL 语句中返回总数、分组统计、分页数据三个结果集，再用 sqlx 依次扫描每个结果集映射到对应结构体。

SQL 示例 #

WITH filtered_requests AS (
    SELECT id, status, created_at, user_id 
    FROM request
    WHERE created_at >= $1 AND status = ANY($2)
)
SELECT COUNT(*) FROM filtered_requests; -- 总数结果集
SELECT status, COUNT(*) AS count FROM filtered_requests GROUP BY status; -- 分组统计结果集
SELECT id, status, created_at, user_id FROM filtered_requests ORDER BY created_at DESC LIMIT $3 OFFSET $4; -- 分页数据结果集

sqlx 代码实现 #

先定义对应的结构体：

type Request struct {
    ID        int64     `db:"id"`
    Status    string    `db:"status"`
    CreatedAt time.Time `db:"created_at"`
    UserID    int64     `db:"user_id"`
}

type StatusCount struct {
    Status string `db:"status"`
    Count  int    `db:"count"`
}

然后处理多结果集：

ctx := context.Background()
startDate := time.Date(2024, 1, 1, 0, 0, 0, 0, time.UTC)
statuses := []string{"pending", "completed"}
limit := 20
offset := 0

query := `
WITH filtered_requests AS (
    SELECT id, status, created_at, user_id 
    FROM request
    WHERE created_at >= $1 AND status = ANY($2)
)
SELECT COUNT(*) FROM filtered_requests;
SELECT status, COUNT(*) AS count FROM filtered_requests GROUP BY status;
SELECT id, status, created_at, user_id FROM filtered_requests ORDER BY created_at DESC LIMIT $3 OFFSET $4;
`

rows, err := db.QueryContext(ctx, query, startDate, statuses, limit, offset)
if err != nil {
    // 处理错误
}
defer rows.Close()

// 读取总数
var total int
if rows.Next() {
    if err := rows.Scan(&total); err != nil {
        // 处理错误
    }
}

// 切换到分组统计结果集
rows.NextResultSet()
var statusCounts []StatusCount
for rows.Next() {
    var sc StatusCount
    if err := rows.Scan(&sc.Status, &sc.Count); err != nil {
        // 处理错误
    }
    statusCounts = append(statusCounts, sc)
}

// 切换到分页数据结果集
rows.NextResultSet()
var requests []Request
for rows.Next() {
    var req Request
    if err := rows.Scan(&req.ID, &req.Status, &req.CreatedAt, &req.UserID); err != nil {
        // 处理错误
    }
    requests = append(requests, req)
}

// 检查所有结果集的遍历错误
if err := rows.Err(); err != nil {
    // 处理错误
}

方案二：会话级临时表 #

PostgreSQL 的临时表默认是会话级的，无需显式事务，只要在同一个连接内即可访问。你可以先创建临时表存储过滤结果，再基于临时表执行三个查询，避免重复扫描原表。

sqlx 代码实现 #

ctx := context.Background()
startDate := time.Date(2024, 1, 1, 0, 0, 0, 0, time.UTC)
statuses := []string{"pending", "completed"}
limit := 20
offset := 0

// 从连接池获取单独连接（临时表仅在该连接内有效）
conn, err := db.Connx(ctx)
if err != nil {
    // 处理错误
}
defer conn.Close()

// 创建临时表并写入过滤结果
_, err = conn.ExecContext(ctx, `
CREATE TEMP TABLE filtered_requests AS
SELECT id, status, created_at, user_id 
FROM request
WHERE created_at >= $1 AND status = ANY($2)
`, startDate, statuses)
if err != nil {
    // 处理错误
}

// 查询总数
var total int
err = conn.QueryRowContext(ctx, "SELECT COUNT(*) FROM filtered_requests").Scan(&total)
if err != nil {
    // 处理错误
}

// 查询分组统计
var statusCounts []StatusCount
rows, err := conn.QueryContext(ctx, "SELECT status, COUNT(*) AS count FROM filtered_requests GROUP BY status")
if err != nil {
    // 处理错误
}
defer rows.Close()
for rows.Next() {
    var sc StatusCount
    if err := rows.Scan(&sc.Status, &sc.Count); err != nil {
        // 处理错误
    }
    statusCounts = append(statusCounts, sc)
}
if err := rows.Err(); err != nil {
    // 处理错误
}

// 查询分页数据
var requests []Request
rows, err = conn.QueryContext(ctx, `
SELECT id, status, created_at, user_id 
FROM filtered_requests 
ORDER BY created_at DESC LIMIT $1 OFFSET $2
`, limit, offset)
if err != nil {
    // 处理错误
}
defer rows.Close()
for rows.Next() {
    var req Request
    if err := rows.Scan(&req.ID, &req.Status, &req.CreatedAt, &req.UserID); err != nil {
        // 处理错误
    }
    requests = append(requests, req)
}
if err := rows.Err(); err != nil {
    // 处理错误
}

方案对比 #

内容的提问来源于stack exchange，提问作者Nikola-Milovic