跨表搜索与分页：FastAPI + PostgreSQL 的一次查询改造

业务列表里的搜索，最容易在数据少的时候显得正确。把当前页拿到的数组过滤一下，输入编号后也确实出现了结果。等记录超过一页，用户搜索另一页的文件名，就会得到“没有找到”。

在询价列表的一次改造中，搜索范围需要同时覆盖主记录编号、关联上传资料的料号和文件名。这里真正要改变的是数据库查询范围：先在目标记录集合里搜索，再返回其中一页。

本文使用简化表结构和合成数据解释方案。代码保留 SQLAlchemy 的表达方式，省略 FastAPI 路由、权限与响应模型，不能作为完整接口直接部署。

先确定搜索的对象

假设系统有两张表：

表	主要字段	关系
inquiry	id、bill_no、created_at	一条询价记录
upload	id、inquiry_id、pn、file_names	一条询价可关联多个上传记录

用户想找的是询价记录，文件名和料号只是寻找它的线索。因此接口返回的单位仍然是主记录，不能因为两个附件都匹配，就把一条询价显示两遍。

测试数据可以准备 25 条主记录，每页 10 条，让其中一条较旧记录关联 demo_100%_rev_a.zip。再让同一条记录的两个附件都包含 sensor。这样能同时检查跨页命中、字面通配符和重复匹配，避免只用当前页的三个样例验证功能。

用 EXISTS 表达关联记录的存在

直接 JOIN 能表达跨表筛选，但一对多关系会扩张结果行。如果主记录有三条匹配附件，它可能在 SQL 结果中出现三次；在 LIMIT 之后再靠程序去重，页面条数和总数都可能出问题。

这个场景只需要回答“是否存在一条匹配资料”，相关 EXISTS 更贴近需求。下面展示简化后的查询：

from sqlalchemy import exists, func, or_, select

def filtered_inquiries(term: str):
    stmt = select(Inquiry)
    term = term.strip()
    if not term:
        return stmt

    upload_matches = exists(
        select(Upload.id).where(
            Upload.inquiry_id == Inquiry.id,
            or_(
                Upload.pn.icontains(term, autoescape=True),
                func.array_to_string(Upload.file_names, "\n")
                    .icontains(term, autoescape=True),
            ),
        )
    )
    return stmt.where(or_(
        Inquiry.bill_no.icontains(term, autoescape=True),
        upload_matches,
    ))

Inquiry 和 Upload 是示意 ORM 模型，file_names 假设是 PostgreSQL 文本数组。这里把数组展开为可搜索文本，适合解释当前需求；如果文件名需要独立权限、更多属性或专门索引，也可以单独建表。

autoescape=True 用于把用户输入中的 % 和 _ 按字面值处理。它与绑定参数各司其职：绑定参数处理 SQL 值的传递，LIKE 转义处理通配符语义。不能因为查询没有字符串拼接，就忽略用户输入 100% 时到底想匹配什么。SQLAlchemy 的字符串包含与转义文档可用于核对具体 API 行为。

编号规则需要单独定义

有些界面编号是 Q-002455，底层主键却是整数。用户输入带前缀、没有前缀或有前导零的编号时，是否都应该命中，需要先定规则。

如果支持编号识别，应使用明确的格式与合理范围进行转换，再把主键条件加入搜索。不要尝试把任何包含数字的文件名都解释为主键，也不要让一个特别长的数字触发无边界的类型转换。

实际接口还要先限定当前用户能看到的主记录，然后在同一权限范围内叠加搜索条件。EXISTS 能防止行数膨胀，却不会自动替系统完成访问控制。

筛选、排序，再分页

得到筛选后的查询后，再添加顺序和分页：

filtered = filtered_inquiries(search)
page_stmt = (
    filtered
    .order_by(Inquiry.created_at.desc(), Inquiry.id.desc())
    .offset((page - 1) * page_size)
    .limit(page_size)
)
count_stmt = select(func.count()).select_from(filtered.subquery())

page 和 page_size 应在接口层限制为正数，并设置页大小上限。总数查询与列表查询要复用同样的筛选和权限条件，否则会出现“提示有结果，当前页却没有”的不一致。

只按创建时间排序还不够。批量导入可能让多条记录拥有相同时间，因此增加唯一的 id 作为次级排序条件。PostgreSQL 的 LIMIT / OFFSET 文档也要求分页时使用可以确定结果顺序的排序。

这种排序解决的是同一数据集合内的顺序不确定问题。用户翻页时如果持续插入新记录，OFFSET 的位置仍会改变，可能看到重复或漏过一条。需要更强连续性时，可以讨论基于时间与 ID 的游标分页；如果要求一次查询期间的快照一致性，还要进一步考虑事务隔离，而不是把“有唯一排序”当作全部解决。

用反例确认搜索范围

我会先把那条旧记录放到默认第一页之外，再搜索它的文件名。之后让多个附件同时命中，确认列表仍只有一条主记录，且总数为一。输入 %、_、中文、前后空格和空字符串，检查结果是否符合约定。最后再验证编号识别及没有权限的记录。

性能需要另行测量。包含匹配、数组文本转换和跨表条件都可能在数据增大后变贵，应查看实际数据分布和查询计划，再决定是否引入 trigram 索引、规范化文件表或其他检索方案。EXISTS 在这里让语义更清楚，但本文没有同条件基准测试，不能给出提速倍数。

这次改造让我更重视接口的集合语义：搜索范围、返回对象、去重方式和分页顺序必须一起设计。当前页上的一个输入框，背后其实是一份完整的查询契约。