如何在AdonisJS中使用whereIn返回含重复ID的有序查询结果及将指定SQL转换为Lucid查询

我来帮你搞定这两个问题——首先是让查询结果严格遵循给定ID列表的顺序并保留重复项，然后把你提供的SQL转换成Lucid风格的查询。

为什么你的原有查询无法满足需求？ #

你当前用的whereIn+orderBy('validade')有两个核心缺陷：

• whereIn会自动去重，列表里重复的ID（比如1122和4455）只会返回一次结果
• orderBy('validade')是按有效期排序，完全无法匹配你想要的输入ID列表的索引顺序

实现方法：构造带顺序索引的子查询JOIN #

要实现保留重复项+按输入顺序排序，最可靠的方式就是你提到的SQL思路——用UNION ALL构造包含所有目标ID（含重复）的临时表，同时给每个ID加上对应的顺序索引，再和主表JOIN，最后按索引排序。下面是Lucid的实现代码：

安全的参数化版本（避免SQL注入） #

const idList = ['1122', '4455', '55664', '1122', '008', '4455'];

// 1. 构造参数化的子查询模板和对应的参数数组
const subQuerySegments = idList.map(() => `SELECT ? AS ID, ? AS sort_order`);
const subQuery = subQuerySegments.join(' UNION ALL ');
const queryParams = [];

idList.forEach((id, index) => {
  queryParams.push(id, index); // 每个ID对应它在列表中的索引（用于排序）
});

// 2. 执行Lucid查询
const result = await Estoque.query()
  .preload('armazem') // 加载关联的armazem数据
  .joinRaw(`JOIN (${subQuery}) x ON x.ID = estoques.siaId`, queryParams)
  .orderBy('x.sort_order', 'asc'); // 按子查询的顺序索引排序

原理说明 #

• 子查询里的每一行对应你ID列表中的一个元素，同时带上sort_order（即原列表的索引），这样就能保留重复的ID条目
• 通过JOIN把主表的记录和子查询的每一行匹配，重复的ID会生成多条结果
• 最后按sort_order排序，确保结果顺序和你输入的ID列表完全一致

将你提供的SQL转换为Lucid查询 #

你给出的SQL本质上是不带排序索引的版本，用Lucid实现的话可以直接用joinRaw复用这个逻辑：

const idList = ['1122', '4455', '55664', '1122', '008', '4455'];

// 构造UNION ALL子查询
const subQuery = idList.map(id => `SELECT '${id}' AS ID`).join(' UNION ALL ');

// Lucid查询
const result = await Estoque.query()
  .preload('armazem')
  .joinRaw(`JOIN (${subQuery}) x ON x.ID = estoques.siaId`);

不过这个版本没有排序逻辑，如果需要保证结果顺序和输入列表一致，还是建议加上前面提到的sort_order索引并排序。

内容的提问来源于stack exchange，提问作者marcelo.delta