Sequelize基于JOIN结果筛选多对多关联订单并返回全部分类的实现方法

实现方案 #

你可以通过主查询嵌套子查询的方式实现，既可以过滤出符合要求的订单，也能加载订单关联的全部分类，同时原生支持limit/offset分页参数。

方案1：直接拼接子查询（和你提供的原生SQL逻辑完全对齐） #

const { Op, literal } = require('sequelize');

order.findAll({
  // 分页参数正常加即可
  limit: 10,
  offset: 0,
  where: {
    id: {
      [Op.in]: literal(`(
        SELECT order_id 
        FROM order_categories 
        WHERE category_id = 'a437ffab-5085-4fd7-b193-23dfb299aa39'
      )`)
    }
  },
  include: [
    {
      model: models.category,
      as: 'categories'
      // 此处不要加where过滤条件，就能返回所有关联分类
    }
  ]
})

方案2：基于模型生成子查询（更易维护，避免SQL注入风险） #

如果你的多对多中间表已经定义了对应模型orderCategory，推荐用这种写法，不需要硬编码表名：

const { Op } = require('sequelize');

// 生成子查询：查询所有关联了指定分类的订单ID
const filteredOrderIds = models.orderCategory.findAll({
  attributes: ['order_id'],
  where: {
    category_id: 'a437ffab-5085-4fd7-b193-23dfb299aa39'
  },
  raw: true
});

order.findAll({
  limit: 10,
  offset: 0,
  where: {
    id: {
      [Op.in]: filteredOrderIds
    }
  },
  include: [
    {
      model: models.category,
      as: 'categories'
    }
  ]
})

原理解释 #

1. 先通过子查询过滤出所有至少关联了指定分类的订单ID，确保主查询返回的订单都符合筛选要求
2. 关联分类的时候不加任何过滤条件，Sequelize会自动加载这些订单对应的所有关联分类，不会只返回匹配的筛选分类
3. 分页参数直接作用于主订单查询，是数据库层面的分页，不会出现内存分页导致的分页数据错误问题

之前写法的问题 #

你之前将过滤条件写在include的参数里，或者用$categories.id$的跨表条件写法，都会让Sequelize在关联查询分类时自动追加过滤条件，最终只会返回匹配到的单个分类，无法拿到全部分类。

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