jooq 多重集通过多重集上的条件对结果集进行排序

收集 mit jooq 的多重集的第一次经验，我尝试弄清楚如何根据多重集的某些标准对结果集进行排序。 考虑带有产品表的数据结构，每个产品都可以分配任意数量的存储空间。

使用此查询获取产品时：

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

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

感谢您的帮助！

亲切的问候， 安德烈亚斯

依靠MULTISET订购

从 jOOQ 3.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_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)