如何在Sequelize 5.6中用子查询+WHERE条件筛选符合属性的产品

嘿，我来帮你搞定这个Sequelize查询的需求！你要实现的是筛选出同时匹配属性选项11和9的产品，还要关联stores表，刚好对应你给出的SQL逻辑。下面是两种可行的实现方式：

方式一：直接嵌入子查询（简单直观） #

这种方式直接把你原SQL里的子查询用Sequelize.literal嵌入到where条件中，和原生SQL逻辑完全对应，上手很快：

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

// 定义选中的属性选项ID和需要匹配的数量（这里是2，因为选了两个选项）
const selectedOptionIds = [11, 9];
const requiredMatches = selectedOptionIds.length;

const filteredProducts = await Products.findAll({
  // 关联stores表，required: true对应INNER JOIN
  include: [
    {
      model: Stores,
      required: true
    }
  ],
  where: {
    [Op.and]: [
      // 嵌入子查询，判断当前产品匹配的选项数量是否达标
      Sequelize.literal(`(SELECT COUNT(*) FROM productProperties WHERE propertyOptionId IN (${selectedOptionIds.join(',')}) AND productId = Products.id) >= ${requiredMatches}`)
    ]
  }
});

注意点： #

• 这里假设你的模型已经正确设置了关联：Products.hasMany(Stores) 和 Products.hasMany(ProductProperties)
• 如果selectedOptionIds是用户输入的内容，直接拼接字符串有SQL注入风险，这种情况下更推荐下面的方式。

方式二：用Sequelize查询构建器生成子查询（更安全） #

这种方式通过Sequelize的查询API来构建子查询，参数会自动绑定，避免SQL注入问题，适合处理动态的用户输入：

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

const selectedOptionIds = [11, 9];
const requiredMatches = selectedOptionIds.length;

// 先构建子查询的SQL语句
const subQuery = ProductProperties.findAll({
  attributes: [[Sequelize.fn('COUNT', '*'), 'matchCount']],
  where: {
    propertyOptionId: { [Op.in]: selectedOptionIds },
    productId: Sequelize.col('Products.id') // 关联主表的id
  },
  raw: true
});

// 执行主查询
const filteredProducts = await Products.findAll({
  include: [
    {
      model: Stores,
      required: true
    }
  ],
  where: {
    [Op.and]: [
      // 用Sequelize.where来判断子查询的结果是否满足条件
      Sequelize.where(Sequelize.literal(`(${subQuery.getQuery()})`), { [Op.gte]: requiredMatches })
    ]
  }
});

为什么推荐这种？ #

• 所有的参数都是通过Sequelize的参数绑定处理的，不用担心注入问题
• 代码的可维护性更好，比如以后要修改筛选条件，只需要调整子查询的where配置即可

两种方式都能实现你要的SQL逻辑，你可以根据自己的场景选择合适的方案～

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