jooq多集排序结果集通过多集上的条件

Question

收集麻省理工学院jooq的multiset的第一次经验，我试图弄清楚如何根据multiset的一些标准对结果集进行排序。考虑一个带有Product Table的数据结构，可以为每个产品分配任意数量的存储空间。

使用此查询获取产品时：

  List<ProductFilterItem> items =
        dslContext
            .select(
                PRODUCT.ID,
                PRODUCT.NAME
                multiset(
                    select(
                        PRODUCT_STORAGE.ID,
                        PRODUCT_STORAGE.PRODUCT_ID,
                        PRODUCT_STORAGE.STOCK
                        PRODUCT_STORAGE.MIN_STOCK
                    )
                        .from(PRODUCT_STORAGE)
                        .where(PRODUCT_STORAGE.PRODUCT_ID.eq(PRODUCT.ID))
                ).as("storage").convertFrom(r -> r.into(ProductStorageItem.class))
            )
            .from(PRODUCT)
            .where(queryCondition)
            .fetchInto(ProductItem.class)

如何根据多集的条件对结果进行排序。

例如，将那些分配了存储空间的存储放在第一位。

orderBy(inline(3).desc())

给出了想要的结果，但对我来说，不清楚实际的排序比较是做什么的。

如何实现更复杂的排序，例如order by

min(
(PRODUCT_STORAGE.STOCK - PRODUCT_STORAGE.MIN_STOCK)
  .divide(PRODUCT_STORAGE.MIN_STOCK.multiply(0.01))
)

或其他基于股票价值的计算。

谢谢你的帮忙!

致以良好的问候，安德烈亚斯

Answer 1

依赖于MULTISET排序

从SQL3.15开始，使用jOOQ / MULTISET或SQLJSON语言在所有方言中模拟MULTISET。虽然XML文档的顺序是未定义的，但一些SQL方言可能会选择在JSON文档上定义顺序(例如，PostgreSQL为JSONB定义了顺序)。但我不相信在您的情况下依赖这种隐式排序是一个好主意(具体地说，因为jOOQ的仿真可能会微妙地更改以适应某些边缘情况)，所以最好明确地说明您想要什么。

在标准SQL中，您可以再次从MULTISET嵌套集合中提取数据，以便找到像MIN(...)表达式那样的聚合值，但这似乎相当费力，而且目前jOOQ 3.15不支持(有一些标准的SQL操作符可以操作多集，请参阅https://github.com/jOOQ/jOOQ/issues/12031)。

MULTISET_AGG替代方案

既然您希望按某个聚合值对事物进行排序，为什么不使用MULTISET_AGG而不是MULTISET呢？在下面的例子中，您会得到一个等价的结果，并且排序是定义良好的。

List<ProductFilterItem> items = dslContext
    .select(
        PRODUCT.ID,
        PRODUCT.NAME
        multisetAgg(
            PRODUCT_STORAGE.ID,
            PRODUCT_STORAGE.PRODUCT_ID,
            PRODUCT_STORAGE.STOCK
            PRODUCT_STORAGE.MIN_STOCK
        ).as("storage").convertFrom(r -> r.into(ProductStorageItem.class))
    )
    .from(PRODUCT)
    .leftJoin(PRODUCT_STORAGE)
        .on(PRODUCT_STORAGE.PRODUCT_ID.eq(PRODUCT.ID))
    .where(queryCondition)
    .groupBy(PRODUCT.ID, PRODUCT.NAME)
    .orderBy(min(
        PRODUCT_STORAGE.STOCK
            .minus(PRODUCT_STORAGE.MIN_STOCK)
            .divide(PRODUCT_STORAGE.MIN_STOCK.multiply(0.01))
    ))
    .fetchInto(ProductItem.class)