Sequelize多表关联查询时如何限制主表Document的返回数量

当关联查询存在一对多/多对多关系时，Sequelize的常规limit参数会基于关联后的总结果行数进行限制，无法直接获取主表指定数量的记录。针对你的需求，提供两种可行解决方案：

方案一：先筛选主表ID，再查询关联数据 #

先查询出符合条件的前15个Document的ID，再基于这些ID查询完整的关联数据，逻辑清晰且易维护。

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

// 1. 获取符合条件的前15个Document的ID
const documentIds = await Document.findAll({
  attributes: ['id'],
  include: [{
    model: DocumentStatus,
    as: 'status',
    attributes: [] // 仅用于过滤，无需返回字段
  }],
  where: {
    deleted: false,
    '$status.code$': { [Op.lt]: '25' }
  },
  limit: 15,
  order: [['id', 'ASC']],
  raw: true
}).then(rows => rows.map(row => row.id));

// 2. 根据ID查询完整的关联数据
const targetDocuments = await Document.findAll({
  include: [
    { model: DocumentType, as: 'type' },
    { model: DocumentStatus, as: 'status' },
    { model: Document, as: 'documentLinked' },
    { model: DocumentBatch, as: 'batch' },
    { model: Project, as: 'project' },
    { model: Budget, as: 'budget' },
    { model: Document_AircraftConfig, as: 'documentsAircraftConfigs' },
    { model: Attachment, as: 'attachments' },
    { model: DocumentNote, as: 'notes' },
    // 补充多对多的Criterias关联
    {
      model: Criteria,
      as: 'criterias',
      through: { attributes: [] } // 隐藏中间表字段
    }
  ],
  where: {
    id: documentIds,
    deleted: false
  },
  order: [['id', 'ASC']]
});

方案二：手动构建窗口查询（匹配你的PostgreSQL实现） #

通过Sequelize执行原生SQL，直接复用你已有的窗口函数逻辑，确保生成的SQL和你提供的结构一致。

const targetDocuments = await sequelize.query(`
select *
from (
  SELECT  dense_rank() over (order by "Document"."id") as "idx",
          "Document"."type_id", "Document"."reference", "Document"."title",
          "Document"."status_id", "Document"."ata_code", "Document"."ataAH_code",
          "Document"."issue", "Document"."lead_code", "Document"."documentLinked_id",
          "Document"."batch_id", "Document"."project_id", "Document"."creationDateAH",
          "Document"."statusAH", "Document"."budget_id", "Document"."toBeAnalysed",
          "Document"."createdBy_code", "Document"."updatedBy_code", "Document"."id",
          "Document"."deleted", "Document"."createdAt", "Document"."updatedAt",
          "criterias"."code" AS "criterias.code", "criterias"."title" AS "criterias.title",
          "criterias"."createdBy_code" AS "criterias.createdBy_code",
          "criterias"."updatedBy_code" AS "criterias.updatedBy_code", "criterias"."id" AS "criterias.id",
          "type"."id" AS "type.id", "type"."code" AS "type.code",
          "status"."id" AS "status.id", "status"."code" AS "status.code",
          -- 按需添加其他关联表的字段
          "documentsAircraftConfigs"."id" AS "documentsAircraftConfigs.id",
          "notes"."id" AS "notes.id"
  FROM "Documents" AS "Document" 
  LEFT OUTER JOIN ( "Documents_Criterias" AS "criterias->Document_Criteria" 
                    INNER JOIN "Criterias" AS "criterias" 
                    ON "criterias"."id" = "criterias->Document_Criteria"."criteria_id") 
                   ON "Document"."id" = "criterias->Document_Criteria"."document_id" 
  LEFT OUTER JOIN "Documents_AircraftConfigs" AS "documentsAircraftConfigs" 
               ON "Document"."id" = "documentsAircraftConfigs"."document_id" 
  LEFT OUTER JOIN "Document_Notes" AS "notes" 
               ON "Document"."id" = "notes"."document_id" 
  LEFT OUTER JOIN "Document_Status" AS "status" 
               ON "Document"."status_id" = "status"."id" 
  LEFT OUTER JOIN "Document_Types" AS "type" 
               ON "Document"."type_id" = "type"."id" 
  LEFT OUTER JOIN "Projects" AS "project" 
               ON "Document"."project_id" = "project"."id" 
  LEFT OUTER JOIN "Budgets" AS "budget" 
               ON "Document"."budget_id" = "budget"."id" 
  LEFT OUTER JOIN "Documents" AS "documentLinked" 
               ON "Document"."documentLinked_id" = "documentLinked"."id" 
  LEFT OUTER JOIN "Document_Batches" AS "batch" 
               ON "Document"."batch_id" = "batch"."id" 
  
  WHERE "status"."code" < '25' 
    AND "Document"."deleted" = false 
) as "docs"
where "idx" <= 15
`, { type: sequelize.QueryTypes.SELECT });

关于Sequelize内置参数说明 #

目前Sequelize官方并未提供limitOnMainTable这类直接限制主表行数的参数，上述两种方案是当前的主流解决方式：

• 方案一适合大多数场景，代码可读性高、易维护；
• 方案二完全匹配你已有的PostgreSQL实现，适合需要严格对齐原有SQL逻辑的场景。

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