Postgres搭配PHP PDO开发分页时，如何获取带筛选条件的查询结果总行数

现有代码的核心缺陷 #

• 原逻辑在执行带WHERE筛选的分页主查询后，统计行数时直接对orders表做无筛选的全表COUNT，得到的结果是整张表的总记录数，并非带筛选条件下的有效匹配总数，完全不符合分页计算的需求
• PostgreSQL 没有 MySQL 中FOUND_ROWS()的等价内置函数，无法直接跨库复用现有实现逻辑

两种适配技术栈的正确实现方式 #

方案1：同条件独立COUNT查询（通用兼容方案） #

• 核心规则：COUNT查询的筛选逻辑必须和主分页查询的WHERE/JOIN/GROUP BY/HAVING规则完全对齐，复用主查询的筛选参数，避免统计偏差
• 修正后的代码示例：

// 分页参数定义
$pageSize = 10;
$currentPage = 1;
$offset = ($currentPage - 1) * $pageSize;

// 执行带筛选的分页主查询
$mainSql = "SELECT orderdate, shippeddate, shipname, shipaddress, shipcity, shipcountry
            FROM orders
            WHERE shipcountry = :filter_country -- 筛选条件示例
            ORDER BY orderdate DESC
            LIMIT :limit OFFSET :offset";
$result = $pdo->prepare($mainSql);
// 绑定主查询参数
$result->bindValue(':filter_country', '中国', \PDO::PARAM_STR);
$result->bindValue(':limit', $pageSize, \PDO::PARAM_INT);
$result->bindValue(':offset', $offset, \PDO::PARAM_INT);
$result->execute();

// 拉取当前页订单数据
$orders = [];
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
    $orders[] = [
        'orderdate' => $row['orderdate'],
        'shippeddate' => $row['shippeddate'],
        'shipname' => $row['shipname'],
        'shipaddress' => $row['shipaddress'],
        'shipcity' => $row['shipcity'],
        'shipcountry' => $row['shipcountry']
    ];
}

// 修正：COUNT查询携带和主查询完全一致的筛选条件
$countSql = "SELECT COUNT(*) AS total_rows
             FROM orders
             WHERE shipcountry = :filter_country"; -- 筛选逻辑与主查询对齐
$countResult = $pdo->prepare($countSql);
// 复用主查询的筛选参数
$countResult->bindValue(':filter_country', '中国', \PDO::PARAM_STR);
$countResult->execute();
$totalRows = (int)$countResult->fetch(\PDO::FETCH_ASSOC)['total_rows'];

• 注意事项：如果主查询存在多表JOIN、需要去重统计的场景，COUNT语句要替换为COUNT(DISTINCT 订单表主键ID)，避免JOIN产生的重复行导致统计值虚高。

方案2：窗口函数单查询实现（减交互方案） #

• 实现逻辑：在主查询中通过PostgreSQL内置的窗口函数COUNT(*) OVER()，直接计算当前筛选条件下的全量匹配总行数，不需要额外发起一次COUNT请求，减少数据库交互次数
• 代码示例：

// 分页参数定义
$pageSize = 10;
$currentPage = 1;
$offset = ($currentPage - 1) * $pageSize;

$mainSql = "SELECT orderdate, shippeddate, shipname, shipaddress, shipcity, shipcountry,
                   COUNT(*) OVER() AS total_rows -- 窗口函数统计当前WHERE条件下的总匹配数
            FROM orders
            WHERE shipcountry = :filter_country
            ORDER BY orderdate DESC
            LIMIT :limit OFFSET :offset";
$result = $pdo->prepare($mainSql);
$result->bindValue(':filter_country', '中国', \PDO::PARAM_STR);
$result->bindValue(':limit', $pageSize, \PDO::PARAM_INT);
$result->bindValue(':offset', $offset, \PDO::PARAM_INT);
$result->execute();

// 拉取数据时提取总匹配数
$orders = [];
$totalRows = 0;
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
    // 窗口函数计算的total_rows在每一行结果中都一致，只需要提取一次
    if ($totalRows === 0) {
        $totalRows = (int)$row['total_rows'];
    }
    $orders[] = [
        'orderdate' => $row['orderdate'],
        'shippeddate' => $row['shippeddate'],
        'shipname' => $row['shipname'],
        'shipaddress' => $row['shipaddress'],
        'shipcity' => $row['shipcity'],
        'shipcountry' => $row['shipcountry']
    ];
}

• 适用场景与限制：
  • 优势：无筛选逻辑写漏的风险，减少一次数据库请求，适合筛选逻辑复杂、单表数据量不大的场景
  • 局限：当筛选后的匹配数据集达到百万级以上时，窗口函数的全量扫描计算开销会高于独立COUNT查询，大数据集场景更适合用方案1。

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