如何在Knex中按数组原始顺序对查询结果排序？

问题场景 #

我使用Knex执行查询时，通过whereIn指定了一个顺序至关重要的数组：

knex("users").select("id", "name").whereIn("song_num", [4, 1, 34])

但返回结果的顺序是1、4、34，和数组的原始顺序4、1、34不符。我了解MySQL可以用FIELD函数、PostgreSQL可以用ORDINALITY实现按数组原始顺序排序，但我使用的是SQLite，尝试用orderByRaw时程序直接崩溃，想知道Knex中有没有适配SQLite的解决方法？

解决方案：基于SQLite CASE语句的Knex实现 #

SQLite没有内置类似FIELD的排序函数，但可以通过CASE语句为数组中的每个元素分配对应的排序权重，Knex中可以动态生成这个逻辑：

基础写法 #

const targetSongNums = [4, 1, 34];
// 生成CASE语句的WHEN分支
const orderCaseClauses = targetSongNums.map((num, index) => `WHEN song_num = ${num} THEN ${index}`).join(' ');

const sortedResult = await knex("users")
  .select("id", "name")
  .whereIn("song_num", targetSongNums)
  .orderByRaw(`CASE ${orderCaseClauses} END`);

安全防注入写法 #

如果数组中的值来自用户输入，建议用参数绑定避免SQL注入：

const targetSongNums = [4, 1, 34];
// 生成带参数占位符的WHEN分支
const orderCaseClauses = targetSongNums.map((_, index) => `WHEN song_num = ? THEN ${index}`).join(' ');
// 整理绑定参数：每个song_num对应一个占位符，按顺序传入
const bindParams = targetSongNums.flatMap((num, idx) => [num, idx]);

const sortedResult = await knex("users")
  .select("id", "name")
  .whereIn("song_num", targetSongNums)
  .orderByRaw(`CASE ${orderCaseClauses} END`, bindParams);

原理说明 #

这种方法通过CASE语句将数组中的每个元素映射到它在原始数组中的索引位置，排序时按照这个索引值升序排列，就能保证结果和原始数组的顺序完全一致。

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