我明白你的问题了——你之前的过滤条件只是移除了Promotion3黑名单里的那条匹配手机号记录，但并没有把整个Promotion3从结果里排除，对吧？这是因为Sequelize处理嵌套关联的where条件时，默认是过滤关联数据而非主表数据。要实现你要的「排除所有黑名单包含指定手机号的促销活动」，得用存在性检查或者子查询排除ID的方式，下面给你具体方案：

方案一：使用Op.notExists进行存在性检查（推荐） #

这种方式直接让数据库检查「是否存在该促销关联的黑名单包含指定手机号」，如果存在则排除整个促销，性能更优。

代码示例 #

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

const validPromotions = await Promotion.findAndCountAll({
  // 保留你原有的关联配置
  include: [
    {
      model: CustomerList,
      as: 'Blacklist',
      include: [
        {
          model: CustomerPhoneNumber,
          as: 'CustomerPhoneNumbers',
          attributes: ['phone_number'],
        }
      ],
    },
    {
      model: CustomerList,
      as: 'Whitelist',
      include: [
        {
          model: CustomerPhoneNumber,
          as: 'CustomerPhoneNumbers',
          attributes: ['phone_number'],
        }
      ],
    }
  ],
  where: {
    [Op.notExists]: sequelize.literal(`
      SELECT 1 
      FROM BlacklistPromotion bp
      JOIN CustomerList cl ON bp.CustomerListId = cl.id
      JOIN CustomerListPhoneNumber clpn ON cl.id = clpn.CustomerListId
      JOIN CustomerPhoneNumber cpn ON clpn.CustomerPhoneNumberId = cpn.id
      WHERE bp.PromotionId = Promotion.id
        AND cpn.phone_number = '0877777777'
    `)
  }
});

原理说明 #

Op.notExists会生成一个子查询，数据库会对每个Promotion检查：是否存在一条关联的黑名单记录包含指定手机号。如果存在，这个Promotion就会被排除，完全符合你的需求。

如果你不想写原生SQL，也可以用Sequelize的查询构建器来生成子查询：

const subQuery = CustomerPhoneNumber.findOne({
  attributes: [],
  include: [
    {
      model: CustomerList,
      as: 'CustomerLists', // 这里要对应CustomerPhoneNumber与CustomerList的关联别名
      include: [
        {
          model: Promotion,
          as: 'Blacklist', // 对应CustomerList与Promotion的多对多关联别名
          attributes: [],
          where: { [Op.col]: 'Promotion.id' } // 关联主查询的Promotion.id
        }
      ]
    }
  ],
  where: { phone_number: '0877777777' },
  raw: true
});

const validPromotions = await Promotion.findAndCountAll({
  include: [/* 你的Blacklist、Whitelist关联配置 */],
  where: { [Op.notExists]: subQuery }
});

方案二：先查询要排除的Promotion ID，再过滤 #

这种方式更直观，先找出所有黑名单包含指定手机号的Promotion ID，再在主查询中排除这些ID。

代码示例 #

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

// 第一步：获取所有需要排除的Promotion ID
const excludedPromotionIds = await Promotion.findAll({
  attributes: ['id'],
  include: [
    {
      model: CustomerList,
      as: 'Blacklist',
      required: true, // 内连接，只保留有黑名单的Promotion
      include: [
        {
          model: CustomerPhoneNumber,
          as: 'CustomerPhoneNumbers',
          required: true,
          where: { phone_number: '0877777777' }
        }
      ]
    }
  ],
  raw: true
}).then(rows => rows.map(row => row.id));

// 第二步：查询排除这些ID的Promotion
const validPromotions = await Promotion.findAndCountAll({
  include: [/* 你的Blacklist、Whitelist关联配置 */],
  where: {
    id: {
      [Op.notIn]: excludedPromotionIds.length ? excludedPromotionIds : [0] // 处理空数组，避免SQL语法错误
    }
  }
});

原理说明 #

先通过内连接筛选出所有关联了目标手机号黑名单的Promotion，拿到它们的ID后，主查询用Op.notIn排除这些ID，就能得到你要的Promotion1和Promotion2。

为什么你之前的方法无效？ #

你之前用的{'$Blacklist.CustomerPhoneNumbers.phone_number$': { [Op.ne]: '0877777777' }}，本质是在LEFT JOIN后的结果里过滤掉匹配手机号的记录，但Promotion本身依然会被返回——只是它的Blacklist关联数据里少了那条匹配的记录而已。而我们需要的是完全移除整个Promotion，所以必须用顶层的过滤逻辑来实现。

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