Knex.js执行update后如何对returning返回结果做orderBy排序

原写法无效原因 #

Knex.js的update()方法触发后，后续直接链式调用的orderBy()不会被拼接到最终生成的UPDATE语句中。标准SQL的UPDATE语法默认不支持直接在RETURNING子句后追加排序规则，Knex也没有内置对这类扩展语法的默认拼接支持，因此你写的代码无法得到预期的排序结果。

可行实现方案 #

不需要强制拆分为更新、查询两条完全独立的执行语句，根据你使用的数据库类型，可以选择以下单语句原子实现方案：

• 针对PostgreSQL等直接支持UPDATE ... RETURNING ... ORDER BY语法的数据库，可以通过modify()方法手动注入排序逻辑，强制让Knex把排序片段拼接到语句末尾：

const notifs = await db('notification')
  .where({ receiver_id: id })
  .update({ marked: true })
  .returning("*")
  .modify(qb => {
    qb.orderBy("notif_date", "desc")
  })

• 针对所有支持CTE（公共表表达式）和RETURNING特性的数据库（PostgreSQL、SQLite 3.35+、MSSQL等），更通用的写法是通过CTE包裹更新逻辑，外层查询完成排序，整个逻辑是单条SQL原子执行，不会出现并发数据不一致问题：

const notifs = await db.with('updated_rows', qb => {
    qb.table('notification')
      .where({ receiver_id: id })
      .update({ marked: true })
      .returning("*")
  })
  .select('*')
  .from('updated_rows')
  .orderBy('notif_date', 'desc')

如果你使用的是不支持CTE/RETURNING的老版本数据库（如5.7及以下版本MySQL），无法单语句实现该需求，需要将更新和查询逻辑包裹在同一个事务中拆分执行，保证数据一致性。

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