如何在SQLAlchemy中实现PostgreSQL风格的复合WHERE子句分页？

当然可以！SQLAlchemy完全支持这种PostgreSQL风格的行值（复合值）比较，而且实现起来相当直观，完美适配你提到的高效分页方案。

核心实现方式 #

1. 模型定义（先搭好基础）

假设我们有一个代表帖子的模型，对应你示例中的<table>：

from sqlalchemy import Column, Integer, DateTime, String, Index
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    topic_id = Column(Integer)  # 对应你示例中的topic字段
    date = Column(DateTime)
    content = Column(String)

    # 关键：创建匹配查询逻辑的复合索引，确保分页性能
    __table_args__ = (
        Index('idx_posts_topic_date_id', topic_id, date.desc(), id.desc()),
    )

2. 编写行值比较的查询

方式一：使用tuple_()构造器（兼容全版本SQLAlchemy）

如果你还在使用SQLAlchemy 1.3及更早版本，显式用tuple_()是最稳妥的方式：

from sqlalchemy import tuple_
from sqlalchemy.orm import sessionmaker

# 假设已经初始化好session
Session = sessionmaker(bind=engine)
session = Session()

# 上一页最后一条的date和id（从前端或上一次查询结果获取）
prev_date = "2024-05-20 12:00:00"
prev_id = 123

# 构造目标查询
query = (
    session.query(Post)
    .filter(Post.topic_id == 1234)
    .filter(tuple_(Post.date, Post.id) < (prev_date, prev_id))
    .order_by(Post.date.desc(), Post.id.desc())
    .limit(10)
)

# 执行查询获取结果
next_page_posts = query.all()

方式二：直接用Python元组（SQLAlchemy 1.4+ / 2.x推荐）

在新版本的SQLAlchemy中，你可以直接用Python的元组语法，框架会自动转换为PostgreSQL的行值比较语法，代码更简洁：

query = (
    session.query(Post)
    .filter(Post.topic_id == 1234)
    .filter((Post.date, Post.id) < (prev_date, prev_id))
    .order_by(Post.date.desc(), Post.id.desc())
    .limit(10)
)

为什么这种方式比OFFSET/LIMIT高效？ #

正如你提到的，OFFSET/LIMIT需要扫描并跳过前面所有的行，当数据量很大时，性能会急剧下降。而行值比较的方式可以直接利用我们定义的复合索引，精准定位到下一页的起始位置，避免了不必要的全表扫描，分页性能提升非常明显。

内容的提问来源于stack exchange，提问作者David