如何使用Sequelize基于关联表与嵌套[Op.and]实现findAll查询？

要实现同时匹配多个关联属性的查询，你之前的写法错误在于试图让同一条Properties记录满足两个互斥的name条件（同一行不可能同时是make和color）。以下是三种可行的解决方案：

方案一：多次关联Properties表（直观易读） #

通过给Properties表设置不同别名，分别匹配每个属性条件，并用required: true确保两个关联都必须存在：

const cars = await models.Car.findAll({
  include: [
    {
      model: models.Properties,
      as: 'makeProperty', // 别名避免关联冲突
      required: true,
      where: {
        name: 'make',
        value: 'bmw'
      }
    },
    {
      model: models.Properties,
      as: 'colorProperty',
      required: true,
      where: {
        name: 'color',
        value: 'blue'
      }
    }
  ]
});

原理：Sequelize会生成两次JOIN操作，分别匹配对应属性的记录，只有同时满足两个关联条件的Car才会被返回。

方案二：分组+Having子句（性能更优） #

先筛选出包含任一目标属性的Car，再通过分组统计确保两个属性都存在：

const cars = await models.Car.findAll({
  include: [{
    model: models.Properties,
    required: true,
    where: {
      [Sequelize.Op.or]: [
        { name: 'make', value: 'bmw' },
        { name: 'color', value: 'blue' }
      ]
    }
  }],
  group: ['Car.id'], // 按Car主键分组
  having: Sequelize.literal('COUNT(DISTINCT Properties.name) = 2')
});

注意：如果你的业务中同一Car下的name是唯一的（不会重复出现同一属性名），可以把COUNT(DISTINCT Properties.name)简化为COUNT(*) = 2。

方案三：EXISTS子查询（贴近原生SQL逻辑） #

直接使用原生SQL的EXISTS子查询，完全匹配你期望的逻辑：

const cars = await models.Car.findAll({
  where: {
    [Sequelize.Op.and]: [
      Sequelize.literal(`EXISTS (
        SELECT 1 FROM Properties 
        WHERE Properties.carId = Car.id 
        AND Properties.name = 'make' AND Properties.value = 'bmw'
      )`),
      Sequelize.literal(`EXISTS (
        SELECT 1 FROM Properties 
        WHERE Properties.carId = Car.id 
        AND Properties.name = 'color' AND Properties.value = 'blue'
      )`)
    ]
  },
  include: [{ model: models.Properties }] // 可选：返回关联的属性数据
});

原理：每个子查询独立检查Car是否存在对应的属性记录，逻辑清晰，适合复杂查询场景。

注意事项 #

• 确保Car和Properties模型的关联外键（比如carId）定义正确，否则查询会出现关联错误。
• 方案一中的别名as必须唯一，避免Sequelize抛出关联冲突的错误。

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