Sequelize关联查询：如何返回满足子表条件的父表及全部子表数据

问题根因 #

你原有写法将子表Items的过滤条件直接写在include的where配置中，会在关联查询阶段直接过滤子表记录，最终只会返回每个订单下匹配条件的子项，无法保留同订单下的其他子项。

实现方案 #

方案1：使用 EXISTS 子查询筛选订单（推荐） #

通过主查询的EXISTS条件筛选出至少包含一个符合要求子项的订单，再正常关联所有子项即可，性能更高，无冗余查询：

const { Op, Sequelize } = require('sequelize');
let term = 'phone';

Orders.findAll({
  where: {
    [Op.exists]: Sequelize.literal(`(
      SELECT 1 FROM Items 
      WHERE Items.orderId = Orders.id 
      AND Items.itemName LIKE '%${term}%'
    )`)
  },
  // 此处关联不加过滤条件，返回订单下所有子项
  include: [Item]
})

如果担心SQL注入风险，可以参数化处理动态值：

[Op.exists]: Sequelize.literal(`
  SELECT 1 FROM Items 
  WHERE Items.orderId = Orders.id 
  AND Items.itemName LIKE ?
`, [`%${term}%`])

方案2：双关联查询（无需写原生SQL） #

通过两次关联子表实现，第一次关联加过滤条件筛选符合要求的订单，第二次关联返回全部子项：

const { Op } = require('sequelize');
let term = 'phone';

Orders.findAll({
  include: [
    {
      model: Item,
      where: {itemName: { [Op.like]: `%${term}%` }},
      required: true, // 内连接，仅保留有匹配子项的订单
      attributes: [] // 不返回本次关联的子项结果，避免冗余
    },
    {
      model: Item // 第二次关联无过滤条件，返回订单所有子项
    }
  ],
  distinct: true // 避免重复返回订单记录
})

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