基于多集条件的jooq多集序结果集

Question

收集了mit Jooq的multiset的第一次经验，我试图弄清楚如何根据multiset的一些标准来排序结果集。考虑一个带有Product表的datastructure，每个产品可以分配任意数量的存储。

使用以下查询获取产品时：

  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())

给出所需的结果，但对我来说，不清楚实际的排序比较在做什么。

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

谢谢你的帮助！

问候你，安德烈亚斯

Answer 1

从JOOQ3.15开始，multiset在所有方言中都使用SQL/XML或SQL/JSON进行仿真。虽然XML文档的顺序没有定义，但一些SQL方言可能会选择在JSON文档上定义顺序（例如，PostgreSQL为JSONB)定义顺序）。但我不认为在您的情况下依赖这种隐式排序是一个好主意（特别是，因为Jooq的仿真可能会发生微妙的变化以适应一些边缘情况），所以最好明确地说明您想要什么。

在标准SQL中，您可以再次从multiset嵌套集合中提取数据，以便找到一些聚合值，如min(...)表达式，但这似乎相当费力，而且目前jOOQ 3.15不支持（有一些标准SQL操作符可以对多组集进行操作，请参见https://github.com/jOOQ/jOOQ/issues/12031)

既然要按某个聚合值排序，为什么不使用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)