jOOQ使用multiset时如何基于multiset内规则对查询结果集排序

你当前使用的orderBy(inline(3).desc())是按SELECT子句的第3个列（即storage multiset列）做降序排序。支持multiset特性的数据库对集合类型的默认排序规则通常为空集合 < 非空集合，集合元素越多排序权重越高，因此降序排列时带库存记录的产品会排在无库存产品前面，刚好匹配你的需求。
这个写法不推荐长期使用：一是强依赖SELECT列的位置，后续调整查询字段时容易出错；二是不同数据库对集合的排序规则存在差异，可移植性差。

要实现基于库存的自定义计算排序，无需修改现有multiset结构，直接通过关联子查询计算聚合值即可，有两种常用实现方式：

方式1：新增独立排序计算列（推荐，可读性与可维护性更高） #

在SELECT列表中新增专门用于排序的计算列，该列不需要在你的ProductItem实体类中定义，jOOQ映射时会自动忽略不存在的字段：

List<ProductItem> 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)),
                // 新增排序用计算列：计算所有库存的安全率最小值
                field(select(
                    min(
                        PRODUCT_STORAGE.STOCK.sub(PRODUCT_STORAGE.MIN_STOCK)
                            .divide(PRODUCT_STORAGE.MIN_STOCK.multiply(0.01))
                    )
                )
                .from(PRODUCT_STORAGE)
                .where(PRODUCT_STORAGE.PRODUCT_ID.eq(PRODUCT.ID))
                ).as("stock_safety_rate")
            )
            .from(PRODUCT)
            .where(queryCondition)
            // 按计算列排序，无库存的产品计算值为null，用nullsLast调整到末尾
            .orderBy(field(name("stock_safety_rate")).asc().nullsLast())
            .fetchInto(ProductItem.class);

方式2：直接在ORDER BY中写计算逻辑（无需修改SELECT字段） #

如果不想调整现有SELECT列结构，也可以把计算逻辑直接写在ORDER BY子句中：

List<ProductItem> 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)
            .orderBy(
                field(select(
                    min(
                        PRODUCT_STORAGE.STOCK.sub(PRODUCT_STORAGE.MIN_STOCK)
                            .divide(PRODUCT_STORAGE.MIN_STOCK.multiply(0.01))
                    )
                )
                .from(PRODUCT_STORAGE)
                .where(PRODUCT_STORAGE.PRODUCT_ID.eq(PRODUCT.ID))
                ).asc().nullsLast()
            )
            .fetchInto(ProductItem.class);

所有基于库存的排序都可以套用关联子查询+聚合函数的逻辑：

• 优先展示有库存的产品（替代现有按集合排序的不稳定写法）：

  orderBy(field(exists(
      selectFrom(PRODUCT_STORAGE).where(PRODUCT_STORAGE.PRODUCT_ID.eq(PRODUCT.ID))
  )).desc())
  

• 按总库存降序排序：
  把聚合函数替换为sum(PRODUCT_STORAGE.STOCK)即可
• 优先展示存在库存低于警戒值的产品：

  orderBy(field(select(
      max(when(PRODUCT_STORAGE.STOCK.lt(PRODUCT_STORAGE.MIN_STOCK), 1).otherwise(0))
  )
  .from(PRODUCT_STORAGE)
  .where(PRODUCT_STORAGE.PRODUCT_ID.eq(PRODUCT.ID))).desc())
  

内容的提问来源于stack exchange，提问作者Draess