一对多关系分页时单边计数错误的JOOQ查询修正方案

问题根源 #

1. 原查询通过LEFT JOIN BALL产生笛卡尔积：每个包含多个红球的盒子会被重复返回，count().over()统计的是重复后的总行数，而非实际符合条件的盒子数量。
2. PostgreSQL不支持窗口函数中使用countDistinct()，这是报错的直接原因。

解决方案 #

方法1：使用EXISTS子查询筛选符合条件的盒子（推荐） #

通过EXISTS先过滤出包含红球的盒子，从根源避免笛卡尔积，此时count().over()能直接得到正确的盒子总数，分页结果也不会出现重复的盒子记录：

Map<Integer, List<Box>> result = ctx.select(
        Box.asterisk(),
        count().over().as("total")
    )
    .from(BOX)
    .where(exists(
        selectOne()
        .from(BALL)
        .where(BALL.BOX_ID.eq(BOX.ID))
        .and(BALL.COLOR.eq("red"))
    ))
    .orderBy(BOX.ID)
    .limit(size)
    .offset(size * page)
    .fetchGroups(field("total", Integer.class), record -> {
        // 替换为你的DTO映射逻辑
        return record.into(Box.class);
    });

方法2：先对关联表去重再关联 #

先通过子查询对BALL表按BOX_ID分组过滤，得到所有包含红球的盒子ID，再与BOX表关联，同样能避免笛卡尔积：

Map<Integer, List<Box>> result = ctx.select(
        Box.asterisk(),
        count().over().as("total")
    )
    .from(BOX)
    .join(
        select(BALL.BOX_ID)
        .from(BALL)
        .where(BALL.COLOR.eq("red"))
        .groupBy(BALL.BOX_ID)
    ).on(BOX.ID.eq(field(BALL.BOX_ID)))
    .orderBy(BOX.ID)
    .limit(size)
    .offset(size * page)
    .fetchGroups(field("total", Integer.class), record -> {
        // 替换为你的DTO映射逻辑
        return record.into(Box.class);
    });

原方案不可行的原因 #

• 第一个查询中selectDistinct是在窗口函数统计之后生效的，count().over()计算的是JOIN后的总行数（含重复盒子），而非去重后的盒子数量。
• PostgreSQL的窗口函数不支持countDistinct()语法，这是数据库层面的限制，无法直接通过修改窗口函数参数解决。

内容的提问来源于stack exchange，提问作者Михаил Катков