5.3 用SQL完成筛选关联与聚合

用SQL完成筛选关联与聚合

文章列表、作者文章数量和文章详情都需要筛选、关联或统计。如果先把所有文章读进 Python，再用循环查作者、计数和拼装响应，数据量一大就会出现多次往返和 N+1 查询。更稳妥的边界是：数据库负责筛选、JOIN、聚合和分页，Python 负责把已经得到的行转换成对外模型。

这不是“所有逻辑都必须写 SQL”。字段格式、权限判断和结果的最后一层转换仍属于应用；但能由数据库一次完成的过滤和聚合，不应在应用层反复扫描。下面使用 SQLAlchemy 2 Core 的 select()、join()、outerjoin()、group_by()、limit() 和 offset()，不使用旧式 Session.query()。

先写清楚查询形状

本节需要两类结果：

1. 某个作者的文章列表：只返回属于该作者的文章，关联作者显示名，按更新时间和 ID 稳定降序分页。
2. 所有作者的文章数量：即使作者没有文章也要返回，数量应为 0。

分页参数必须经过边界检查，查询必须使用绑定参数，不要用字符串拼接把作者 ID 或排序值写进 SQL。排序至少要有一个稳定的唯一键作为并列时的第二排序条件，否则同一页数据在多次请求之间可能顺序不稳定。

<!-- file: ch05_queries/query_shapes.py -->

from __future__ import annotations

from typing import Any

from sqlalchemy import (
    Column,
    DateTime,
    Integer,
    MetaData,
    String,
    Table,
    bindparam,
    func,
    select,
)
from sqlalchemy.dialects import postgresql


metadata = MetaData()
users = Table(
    "users",
    metadata,
    Column("id", Integer),
    Column("display_name", String(80)),
)
posts = Table(
    "posts",
    metadata,
    Column("id", Integer),
    Column("slug", String(160)),
    Column("title", String(120)),
    Column("author_id", Integer),
    Column("created_at", DateTime(timezone=True)),
    Column("updated_at", DateTime(timezone=True)),
)


def page_values(limit: int, offset: int) -> tuple[int, int]:
    if not 1 <= limit <= 100:
        raise ValueError("limit 必须在 1 到 100 之间")
    if offset < 0:
        raise ValueError("offset 不能为负数")
    return limit, offset


def posts_for_author(
    author_id: int,
    *,
    limit: int = 20,
    offset: int = 0,
) -> tuple[Any, dict[str, int]]:
    limit, offset = page_values(limit, offset)
    statement = (
        select(
            posts.c.id,
            posts.c.slug,
            posts.c.title,
            posts.c.created_at,
            users.c.display_name.label("author_name"),
        )
        .select_from(posts.join(users, posts.c.author_id == users.c.id))
        .where(posts.c.author_id == bindparam("author_id"))
        .order_by(posts.c.updated_at.desc(), posts.c.id.desc())
        .limit(limit)
        .offset(offset)
    )
    return statement, {"author_id": author_id}


def author_post_counts() -> Any:
    # LEFT JOIN 加上 count(posts.id)，没有文章的作者才能得到 0。
    return (
        select(
            users.c.id,
            users.c.display_name,
            func.count(posts.c.id).label("post_count"),
        )
        .select_from(users.outerjoin(posts, posts.c.author_id == users.c.id))
        .group_by(users.c.id, users.c.display_name)
        .order_by(users.c.id)
    )


def assemble_portable_summary(rows: list[dict[str, Any]]) -> list[dict[str, Any]]:
    """把一次 SQL 返回的扁平行组装成嵌套结果；不再为每篇文章查一次作者。"""
    result: dict[int, dict[str, Any]] = {}
    for row in rows:
        author = result.setdefault(
            row["author_id"],
            {
                "id": row["author_id"],
                "name": row["author_name"],
                "posts": [],
            },
        )
        if row["post_id"] is not None:
            author["posts"].append(
                {"id": row["post_id"], "title": row["title"]}
            )
    return list(result.values())


def self_check() -> None:
    dialect = postgresql.dialect()
    statement, params = posts_for_author(42, limit=10, offset=20)
    list_sql = str(
        statement.compile(
            dialect=dialect,
            compile_kwargs={"render_postcompile": True},
        )
    )
    count_sql = str(author_post_counts().compile(dialect=dialect))

    lower_list_sql = list_sql.lower()
    lower_count_sql = count_sql.lower()
    assert "join users" in lower_list_sql
    assert "limit" in lower_list_sql and "offset" in lower_list_sql
    assert "author_id" in lower_list_sql
    assert "42" not in list_sql
    assert params == {"author_id": 42}
    assert "left outer join posts" in lower_count_sql
    assert "count(posts.id)" in lower_count_sql
    assert "group by" in lower_count_sql

    try:
        posts_for_author(42, limit=0)
    except ValueError as exc:
        assert "limit" in str(exc)
    else:
        raise AssertionError("limit=0 应该被拒绝")

    summary = assemble_portable_summary(
        [
            {"author_id": 10, "author_name": "Alice", "post_id": None, "title": None},
            {"author_id": 20, "author_name": "Bob", "post_id": 1, "title": "文章"},
        ]
    )
    assert summary == [
        {"id": 10, "name": "Alice", "posts": []},
        {"id": 20, "name": "Bob", "posts": [{"id": 1, "title": "文章"}]},
    ]
    print("query_shapes self-check passed; SQL compiled without a database")


if __name__ == "__main__":
    self_check()

运行本地检查：

python ch05_queries/query_shapes.py

预期输出包含 SQL compiled without a database。这个检查验证 SQLAlchemy 2 查询结构、绑定参数、分页边界和无文章作者的本地组装逻辑，没有连接 PostgreSQL，也没有测量查询耗时或索引命中情况。

JOIN、LEFT JOIN 和计数的区别

文章列表使用内连接，因为它只需要返回确实存在作者的文章；如果数据库已经有外键，这个关系通常成立。作者统计使用 LEFT JOIN，因为左侧作者即使没有右侧文章也必须保留。此时应写 count(posts.id)：没有匹配行时，右表的 posts.id 是 NULL，计数为 0。如果写 count(*)，左表的作者仍会形成一行，得到的可能是 1，统计语义就错了。

过滤条件的位置也会改变结果。把 posts 的限制放到 WHERE，可能把 LEFT JOIN 变成只保留有文章的作者；如果要保留零文章作者，应把关系条件放在 ON，并谨慎选择额外筛选条件。先写出希望保留哪些行，再决定条件属于 JOIN ... ON 还是 WHERE。

稳定分页和参数化查询

limit() 和 offset() 是 SQLAlchemy 2 的查询构造方法。示例先限制 limit 的上限，再按 updated_at DESC, id DESC 排序，最后应用分页。更新时可能发生页漂移：offset 分页适合简单列表，但在大量新增或频繁更新的数据上，后续可以评估基于最后一条记录的 keyset pagination；这不是本节可以凭空承诺的性能优化。

绑定参数让驱动负责值的编码和转义，避免用 f-string 拼 SQL。示例通过 bindparam("author_id") 显式命名参数，编译出的 SQL 不包含作者 ID 的字面值。真实执行时，把参数作为执行参数传给 session.execute(statement, params)，不要自行替换占位符。

聚合和 Python 组装各做一部分

数据库擅长筛选、JOIN 和 COUNT 等聚合，应该先把不需要的行过滤掉。响应需要嵌套作者和文章时，SQLAlchemy 可以返回扁平行，Python 再做一次线性组装；这比先取所有文章、再对每篇文章查询作者少很多往返。

PostgreSQL 还提供 json_agg、json_build_object 等 JSON 聚合函数，但它们是数据库方言能力。若产品决定使用它们，应在文档和测试中明确 PostgreSQL 依赖，并为迁移到其他数据库保留可移植的行结果或 Python 组装方案。本节选择后者，所以没有把 PostgreSQL JSON 结果假定成通用 SQLAlchemy 返回形状。

N+1 与性能结论需要实测

下面的写法容易产生 N+1：先查询 20 篇文章，再在 Python 循环中对每个 author_id 发一次作者查询。改成一个 JOIN 可以减少往返，但最终效果仍受索引、数据分布、网络、连接池和执行计划影响。教程只验证生成的查询结构，不声称它在任何数据量上更快。

要在真实环境检查，使用隔离 PostgreSQL 数据库和代表性数据，先执行迁移，再对实际查询运行 EXPLAIN (ANALYZE, BUFFERS)。记录查询计划和数据规模后再决定索引或分页策略，不要把一次本地编译结果当成性能基准。

资料来源

• 主要参考：fastapi-best-practices 中文 README 的“SQL 优先，Pydantic 次之”主题。本节保留数据库筛选、关联和聚合的方向，改用 SQLAlchemy 2 参数化查询，并补充 LEFT JOIN、零计数和可移植组装边界。
• 官方文档：SQLAlchemy 2.0 SELECT 与关联、SQLAlchemy 2.0 SELECT 构造、PostgreSQL 教程。