Sequelize多关联查询：筛选含指定B的A并返回全部B关联

需求 #

我有两个Sequelize模型A和B，二者为多对多关联。需要查询所有关联了B.id = 'some string'的A实例，但返回的每个A必须包含其所有关联的B实例，无论B.id是否等于'some string'。

错误尝试1：WHERE直接关联B导致子查询异常 #

使用以下代码会报错，原因是子查询中未正确关联B表：

A.findAll({
  where: {'$B.id$':'some string'},
  include: [{model:B, through: {attributes: []}},{model:C, required:true}]
})

对应的伪SQL存在明显问题：WHERE条件里的B.id在子查询中未关联任何表：

SELECT *
FROM (
  SELECT *
  FROM A
  INNER JOIN C ON A.cId = C.id
  WHERE B.id = 'some string' -- 此处错误：子查询未关联B表
) as A
LEFT OUTER JOIN (
  SELECT *
  FROM B
  INNER JOIN "AtoB" ON "AtoB".bId = B.id
) as Bs ON Bs.aId = A.id

错误尝试2：INCLUDE中加WHERE过滤关联数据 #

如果使用下面的代码，返回的A只会包含B.id = 'some string'的关联实例，不符合“返回所有关联B”的需求：

A.findAll({
  include: [{model:B, through: {attributes: []}, where:{id:'some string'}},{model:C, required:true}]
})

正确解决方案 #

要实现需求，需先通过关联查询筛选出符合条件的A，再完整关联这些A的所有B实例。对应的SQL逻辑如下：

SELECT *
FROM (
  SELECT A.*, C.*
  FROM A
  INNER JOIN C ON A.cId = C.id
  INNER JOIN "AtoB" ON "AtoB".aId = A.id
  INNER JOIN B ON B.id = "AtoB".bId
  WHERE B.id = 'some string' -- 筛选出关联了指定B的A
) as filtered_A
LEFT OUTER JOIN (
  SELECT B.*, "AtoB".aId
  FROM B
  INNER JOIN "AtoB" ON "AtoB".bId = B.id
) as all_Bs ON all_Bs.aId = filtered_A.id

Sequelize代码实现 #

方式1：分两步查询

先获取符合条件的A的ID列表，再查询这些A的完整信息并包含所有关联B：

// 第一步：获取关联了指定B的A的ID
const targetAIds = await A.findAll({
  attributes: ['id'],
  include: [
    { model: B, where: { id: 'some string' }, through: { attributes: [] } },
    { model: C, required: true }
  ],
  raw: true
}).then(rows => rows.map(row => row.id));

// 第二步：查询这些A的完整数据，包含所有关联B
const result = await A.findAll({
  where: { id: targetAIds },
  include: [
    { model: B, through: { attributes: [] } },
    { model: C, required: true }
  ]
});

方式2：使用子查询作为WHERE条件

通过Sequelize的literal编写子查询，直接在主查询中筛选符合条件的A：

const result = await A.findAll({
  where: {
    id: {
      [Sequelize.Op.in]: Sequelize.literal(`
        SELECT "A".id
        FROM "A"
        INNER JOIN "C" ON "A"."cId" = "C".id
        INNER JOIN "AtoB" ON "AtoB"."aId" = "A".id
        INNER JOIN "B" ON "B".id = "AtoB"."bId"
        WHERE "B".id = 'some string'
      `)
    }
  },
  include: [
    { model: B, through: { attributes: [] } },
    { model: C, required: true }
  ]
});

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