Knex查询：如何按WHERE条件分组并分别排序结果？

我用Knex查询数据库时，需要对结果做特定的分组排序。当前代码能返回符合条件的结果，但顺序不符合预期（注：原代码存在引号语法错误，已修正）：

knex("FRUITTBL")
  .select("FruitTag", "FruitName", "FruitDescription")
  .whereLike("FruitTag", `${query}%`)
  .orWhereLike("FruitName", `${query}%`)
  .orWhereLike("FruitTag", `%${query}%`)
  .limit(20)
  .offset(`${page}`)
  .orderBy("FruitTag")
  .orderBy("FruitName")
  .orderBy("FruitTag")
  .distinct()

预期需求 #

我希望结果按以下规则排序：

• 先返回匹配whereLike("FruitTag", ${query}%)的结果，组内按FruitTag字母顺序排序
• 接着返回匹配orWhereLike("FruitName", ${query}%)的结果，组内按FruitName字母顺序排序
• 最后返回匹配orWhereLike("FruitTag", %${query}%)的结果，组内按FruitTag字母顺序排序

尝试过程中的问题 #

• 直接调整orderBy顺序后，无法实现分组优先级的效果
• 使用orderByRaw后，分组顺序正确，但第二组（匹配FruitName的结果）内的FruitName未按字母排序
• 调整orderBy与orderByRaw顺序后，组内排序正常，但第二组和第三组的分组顺序颠倒，不符合预期

解决方案 #

要实现这种「分组优先级+组内独立排序」的效果，需要在orderByRaw里明确定义每个条件的优先级权重，再针对每个分组指定对应的排序字段，同时保证SQL逻辑的严谨性。

最终代码如下：

knex("FRUITTBL")
  .select("FruitTag", "FruitName", "FruitDescription")
  .where(function() {
    // 用闭包包裹OR条件，避免和后续可能添加的其他WHERE条件冲突
    this.whereLike("FruitTag", `${query}%`)
      .orWhereLike("FruitName", `${query}%`)
      .orWhereLike("FruitTag", `%${query}%`)
  })
  .distinct()
  .orderByRaw(`
    CASE
      WHEN FruitTag LIKE ? THEN 1
      WHEN FruitName LIKE ? THEN 2
      WHEN FruitTag LIKE ? THEN 3
      ELSE 4
    END,
    CASE
      WHEN FruitTag LIKE ? THEN FruitTag
      WHEN FruitName LIKE ? THEN FruitName
      WHEN FruitTag LIKE ? THEN FruitTag
    END ASC
  `, [`${query}%`, `${query}%`, `%${query}%`, `${query}%`, `${query}%`, `%${query}%`])
  .limit(20)
  .offset(page)

代码说明 #

1. 分组优先级定义：第一个CASE WHEN给每个匹配条件分配权重，值越小优先级越高，确保结果按「FruitTag前缀匹配 → FruitName前缀匹配 → FruitTag包含匹配」的顺序展示
2. 组内排序逻辑：第二个CASE WHEN针对不同分组指定专属排序字段，第一、三组按FruitTag排序，第二组按FruitName排序
3. 安全与严谨性：用参数绑定（?）代替字符串拼接，避免SQL注入；用闭包包裹OR条件，防止后续添加其他WHERE条件时出现逻辑混乱；distinct放在排序前，保证去重后再执行排序逻辑
4. 分页处理：直接传入page变量而非字符串，避免类型转换问题

问题根源解析 #

• 单纯的orderBy只能按固定字段全局排序，无法区分不同匹配条件的分组
• 之前的orderByRaw仅定义了分组优先级，但未针对不同分组指定对应排序字段，导致组内排序不符合要求
• 分组顺序颠倒大概率是CASE WHEN的条件顺序写错，或者排序字段逻辑与分组不匹配

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