如何在Sequelize的where子句关联表筛选不存在的记录并支持分页？

将原有的Node层循环过滤逻辑下沉到数据库层，通过单条SQL完成全量过滤，彻底解决N+1查询问题，同时天然支持分页、排序等扩展能力。

对应MySQL语法 #

要实现“返回kits表中kitID不存在于users表的记录”，优先选择NOT EXISTS子查询实现，稳定性和性能都优于NOT IN写法（不会因子查询返回NULL值导致逻辑失效），对应的原生SQL逻辑为：

SELECT kitID, result, resultDate AS date
FROM kits
WHERE
result IN (1, 2, 3)
AND cp = 0
AND kitID LIKE '%{传入的search值}%'
AND NOT EXISTS (
SELECT 1 FROM users WHERE users.匹配kitID的主键字段 = kits.kitID
)
注意：SQL中的users表匹配字段，需要和原代码中findByPk传入的字段保持一致，也就是之前用来校验kitID是否存在的那个users表主键列

Sequelize 适配代码 #

无需提前定义kits与users的表关联，直接在原有的where条件中加入literal包裹的NOT EXISTS判断即可，完全兼容现有代码结构，可直接扩展分页能力：

async findKitsWithResultNoReg2(_, { search, pageSize = 10, pageIndex = 0 }) {
  try {
    return await db.kits.findAll({
      where: {
        [Op.and]: [
          // 保留原有kits表过滤规则
          { result: { [Op.in]: [1, 2, 3] } },
          { cp: 0 },
          // search非空时才拼接模糊查询，避免空值触发全表扫描
          search ? { kitID: { [Op.like]: `%${search}%` } } : {},
          // 核心新增：SQL层判断kitID未在users表注册
          db.sequelize.literal(`NOT EXISTS (SELECT 1 FROM users WHERE users.id = kits.kitID)`)
        ]
      },
      // 直接配置分页参数即可，SQL层过滤后分页数据完全准确
      offset: pageIndex * pageSize,
      limit: pageSize,
      // 仅查询业务需要的字段，减少数据库查询开销
      attributes: [
        'kitID',
        'result',
        [db.sequelize.col('resultDate'), 'date']
      ]
    })
  } catch (error) {
    console.log(error)
  }
}

注意事项 #

• 不推荐使用NOT IN写法实现过滤：如果users表的匹配字段存在NULL值，会导致整个where条件失效，查询不到任何数据，稳定性远低于NOT EXISTS方案
• 原代码的循环查询是典型的N+1问题：单次查询kits后，循环N次单查users表，数据量上涨后性能会断崖式下跌；且因为过滤逻辑在Node层完成，分页时会出现总条数不准、取数不全的问题
• 请确保给users表中匹配kitID的主键字段建立索引，否则NOT EXISTS子查询会拖慢整体查询速度
• 原代码中用Promise.all包装循环结果的写法无实际作用：循环内的对象生成是同步操作，不存在异步等待，Promise.all并不会提升执行效率

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