如何在Knex中编写带EXISTS条件和IN数组参数的子查询？

完整实现代码 #

// 入参变量可根据业务动态调整
const week_day = 2;
const timeInMinutes = 1320;
const patientIdList = [0, 1]; // 支持传入任意长度的患者ID数组

const caregivers = await db("caregivers")
  // 匹配护理员排班时间条件
  .whereExists(function () {
    this.select("caregiver_schedule.*")
      .from("caregiver_schedule")
      .whereRaw("`caregiver_schedule`.`caregiver_id` = `caregivers`.`id`")
      .whereRaw("`caregiver_schedule`.`week_day` = ??", [Number(week_day)])
      .whereRaw("`caregiver_schedule`.`from_time` <= ??", [timeInMinutes])
      .whereRaw("`caregiver_schedule`.`to_time` > ??", [timeInMinutes]);
  })
  // 匹配关联患者ID在指定列表的条件
  .whereExists(function () {
    this.select("*")
      .from("caregiver_patient")
      // 可选添加：关联外层护理员ID，避免匹配到无关护理员，逻辑更严谨
      // .whereRaw("`caregiver_patient`.`caregiver_id` = `caregivers`.`id`")
      .whereIn("patient_id", patientIdList);
  })
  // 补全所有表关联逻辑，和原生SQL完全对齐
  .join("users", "caregivers.user_id", "=", "users.id")
  .join("caregiver_schedule", "caregivers.id", "=", "caregiver_schedule.caregiver_id")
  .join("caregiver_patient", "caregivers.id", "=", "caregiver_patient.caregiver_id")
  .join("patients", "caregiver_patient.patient_id", "=", "patients.id")
  .select([
    "caregivers.*",
    "users.*",
    "caregiver_schedule.*",
    "patients.*",
  ]);

关键修改说明 #

• 补全了原代码缺失的caregiver_schedule、caregiver_patient两张表的JOIN关联，避免运行时找不到字段报错
• 新增第二个whereExists逻辑，完全对应原生SQL的EXISTS子查询要求，Knex的whereIn方法原生支持数组参数，会自动做参数化处理，无需手动拼接SQL，可避免注入风险
• 所有动态数值（周几、时间、患者ID列表）全部通过变量传入，符合参数化要求
• 可选添加子查询和外层护理员ID的关联条件，可修复原生SQL可能存在的逻辑漏洞，仅返回同时关联了指定患者的护理员，可根据业务需求选择是否启用

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