postgresql 在对一对多关系进行分页时，一侧的计数不正确

hivapdat 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(124)

我尝试实现一对多关系中一方的偏移分页和多方过滤，并通过窗口函数计算总数。
由于笛卡尔积的问题，我得到了指定的球颜色过滤器值的不正确的盒子总数：

Map<Integer, List<Box>> result = ctx.selectDistinct(
Box.asterisk(), count().over().as("total"))
.from(BOX)
.leftJoin(BALL).on(BALL.BOX_ID.eq(BOX.ID))
.where(BALL.COLOR.eq("red"))
.orderBy(BOX.BOX_ID)
.limit(size)
.offset(size * page)
.fetchGroups(field("total", Integer.class), record-{ mapping logic in dto });

我尝试用countDistinct重写jooq查询：

Map<Integer, List<Box>> result = ctx.selectDistinct(
                        Box.asterisk(), countDistinct(BOX.ID).over().as("total"))
                .from(BOX)
                .leftJoin(BALL).on(BALL.BOX_ID.eq(BOX.ID))
                .where(BALL.COLOR.eq("red"))
                .orderBy(BOX.BOX_ID)
                .limit(size)
                .offset(size * page)
                .fetchGroups(field("total", Integer.class), record-{ mapping logic in projection});

并获取：org.postgresql.util.PSQLException：错误：窗口函数未实现DISTINCT
请帮助，我不想写2个单独的jooq查询，以获得总计数和页面。
我想消除重复的单一jooq查询的结果集，并使其正确的全球数量的可用框记录与红球通过一个1选择数据库端的最有效的方法。

postgresql

来源：https://stackoverflow.com/questions/76546477/incorrect-count-of-one-side-during-paginating-one-to-many-relationship

1条答案

按热度按时间

krugob8w1#

您只投影BOX列，使用BALL连接只计算每个盒子的红球。因此，只需使用GROUP BY即可（要知道，DISTINCT的大多数用法可能不是最佳的）：

Map<Integer, List<Box>> result = 
ctx.select(BOX.fields())
   .select(count(BALL.ID).as("total"))
   .from(BOX)
   .leftJoin(BALL)
       .on(BALL.BOX_ID.eq(BOX.ID))
       .and(BALL.COLOR.eq("red"))
   .groupBy(BOX.ID)
   .orderBy(BOX.ID)
   .limit(size)
   .offset(size * page)
   .fetchGroups(field("total", Integer.class), record-{ mapping logic in dto });

几点意见：

这利用了PostgreSQL实现的标准SQL特性，在这里你可以GROUP BY一个主键，并且仍然投影所有功能相关的列。I've blogged about it here。如果您没有使用PostgreSQL，那么只需列出GROUP BY中的所有BOX.fields()。
我已经将您的BALL.COLOR.eq("red") predicate 移到了LEFT JOIN的ON子句see also this blog post I wrote that explains it中。简而言之，如果将该 predicate 留在WHERE子句中，则会将LEFT JOIN变成INNER JOIN，这是您不希望看到的。请注意，在这种情况下，您不能再使用COUNT(*)，而是COUNT(BALL.ID)，以获得BOX es的0计数，而没有BALL s（而不是1计数）。

赞(0）回复(0）举报 2023-06-29

我来回答

postgresql 在对一对多关系进行分页时，一侧的计数不正确

1条答案

相关问题

热门标签

最新问答