如何编写符合关联条件的Sequelize正确分页查询？

问题背景 #

需要查询所有Project数据，但仅保留其当前状态（通过current_project_status_id关联的ProjectStatus）对应的CodePhaseType满足code_status_type_id条件的项目。现有查询能筛选出符合条件的数据，但分页功能异常——分页逻辑在关联筛选前执行，导致后续页码返回空结果。使用Sequelize版本5.21.7。

问题原因 #

核心问题是使用了separate: true配置：该配置会让Sequelize先对Project主表执行分页查询，再对分页后的每条结果单独查询关联表。这就导致主表分页时并未考虑关联筛选条件，后续页码的主表数据可能没有符合关联条件的记录，最终返回空。

解决方案 #

方案一：先筛选符合条件的Project ID，再分页查询

先通过子查询获取所有满足关联条件的project_id，再基于这些ID进行分页查询，确保分页的是已经筛选后的结果：

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

// 第一步：获取所有符合条件的project_id
const validProjectIds = await ProjectStatus.findAll({
  attributes: ['project_id'],
  include: [
    {
      model: CodePhaseType,
      where: { code_status_type_id: +filter },
      required: true
    }
  ],
  where: {
    // 关联Project的current_project_status_id
    project_status_id: {
      [Op.in]: await Project.findAll({
        attributes: ['current_project_status_id']
      }).then(projects => projects.map(p => p.current_project_status_id))
    }
  },
  group: ['project_id'] // 去重，避免同一个project_id多次出现
});

const ids = validProjectIds.map(item => item.project_id);

// 第二步：基于筛选后的ID分页查询Project
const projects = await Project.findAll({
  where: {
    ...where,
    project_id: { [Op.in]: ids }
  },
  limit,
  offset,
  attributes: [
    "project_id",
    "project_name",
    "description",
    "onbase_project_number",
    "created_date",
    'code_project_type_id',
    'current_project_status_id',
  ],
  include: [
    {
      model: ProjectStatus,
      as: 'currentId',
      include: {
        model: CodePhaseType,
        where: { code_status_type_id: +filter },
        required: true
      },
      required: true
    }
  ],
  order: [['created_date', 'DESC']]
});

方案二：修改关联查询配置，使用内连接+去重

去掉separate: true，给所有层级的include添加required: true（将左连接转为内连接），让筛选条件先作用于整个关联结果集，再执行分页。同时添加distinct: true避免重复数据：

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

const projects = await Project.findAll({
  where: where,
  limit,
  offset,
  attributes: [
    "project_id",
    "project_name",
    "description",
    "onbase_project_number",
    "created_date",
    'code_project_type_id',
    'current_project_status_id',
  ],
  include: [
    {
      model: ProjectStatus,
      as: 'currentId',
      required: true, // 只保留有匹配currentId的Project
      include: {
        model: CodePhaseType,
        where: { code_status_type_id: +filter },
        required: true // 只保留符合条件的CodePhaseType
      }
    }
  ],
  order: [['created_date', 'DESC']],
  distinct: true // 去重，避免关联导致的重复Project记录
});

方案三：用子查询直接过滤Project的current_project_status_id

直接通过子查询获取符合条件的project_status_id，再过滤Project的current_project_status_id，再执行分页：

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

// 获取符合条件的project_status_id
const validStatusIds = await ProjectStatus.findAll({
  attributes: ['project_status_id'],
  include: [
    {
      model: CodePhaseType,
      where: { code_status_type_id: +filter },
      required: true
    }
  ]
}).then(items => items.map(item => item.project_status_id));

// 分页查询Project
const projects = await Project.findAll({
  where: {
    ...where,
    current_project_status_id: { [Op.in]: validStatusIds }
  },
  limit,
  offset,
  attributes: [
    "project_id",
    "project_name",
    "description",
    "onbase_project_number",
    "created_date",
    'code_project_type_id',
    'current_project_status_id',
  ],
  include: [
    {
      model: ProjectStatus,
      as: 'currentId',
      include: {
        model: CodePhaseType,
        where: { code_status_type_id: +filter },
        required: true
      },
      required: true
    }
  ],
  order: [['created_date', 'DESC']]
});

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