如何在Sequelize的findAll()中获取关联表的数组结果

问题背景 #

我用Sequelize编写了查询订单列表的代码，其中ProgramOrder是Order与Program的多对多关联表。当前返回结果中programOrders是单个对象，我需要把同一order_id下的所有programOrders以数组形式返回，同时每个programOrders中的program字段也需为数组格式。

当前代码 #

const response = await OrderDal.findAll({
  where: { buyerId: userId },
  attributes: { exclude: ['outcomeId', 'programId'] },
  include: [
    {
      model: User,
      as: 'buyerOrders',
      attributes: ['name'],
    },
    {
      model: User,
      as: 'sellerOrders',
      attributes: ['name'],
    },
    {
      model: Outcome,
      attributes: ['title'],
    },
    {
      model: ProgramOrder,
      as: 'programOrders',
      attributes: ['orderId', 'totalUnitsValue', 'CPO', 'PPO'],
      include: 
        {
          model: Program,
          as: 'program',
          attributes: ['title'],
        },
    },
  ],
  nest: true,
});

当前返回结果 #

"OrderList": [
        {
            "id": 1,
            "buyerId": 1,
            "sellerId": 2,
            "channelPartnerId": 1,
            "updatedById": null,
            "orderNumber": 123,
            "status": "random_status",
            "settlementDate": "2024-02-19T07:32:01.641Z",
            "archiveDate": "2024-02-19T07:32:01.641Z",
            "serviceFee": 1.4,
            "total": 785,
            "orderDate": "2024-02-19T07:32:01.641Z",
            "invoiceDoc": "random_invoice_doc",
            "noOfOutcomes": 7257,
            "createdAt": "2024-02-19T07:32:01.641Z",
            "updatedAt": "2024-02-19T07:32:01.641Z",
            "buyerOrders": {
                "name": "Numaira"
            },
            "sellerOrders": {
                "name": "SELLER"
            },
            "outcome": {
                "title": "Culture of Innovation"
            },
            "programOrders": {
                "orderId": 1,
                "totalUnitsValue": 200,
                "CPO": 0.5,
                "PPO": "0.6",
                "program": {
                    "id": 1,
                    "title": "Quality Education for Refugees"
                }
            }
        },
     ]

期望返回结果 #

"OrderList": [
                {
                    "id": 1,
                    "buyerId": 1,
                    "sellerId": 2,
                    "channelPartnerId": 1,
                    "updatedById": null,
                    "orderNumber": 123,
                    "status": "random_status",
                    "settlementDate": "2024-02-19T07:32:01.641Z",
                    "archiveDate": "2024-02-19T07:32:01.641Z",
                    "serviceFee": 1.4,
                    "total": 785,
                    "orderDate": "2024-02-19T07:32:01.641Z",
                    "invoiceDoc": "random_invoice_doc",
                    "noOfOutcomes": 7257,
                    "createdAt": "2024-02-19T07:32:01.641Z",
                    "updatedAt": "2024-02-19T07:32:01.641Z",
                    "buyerOrders": {
                        "name": "Numaira"
                    },
                    "sellerOrders": {
                        "name": "SELLER"
                    },
                    "outcome": {
                        "title": "Culture of Innovation"
                    },
                    "programOrders": [
                       {
                        "orderId": 1,
                        "totalUnitsValue": 200,
                        "CPO": 0.5,
                        "PPO": "0.6",
                        "program":[
                            {
                                "id": 1,
                                "title": "Quality Education for Refugees"
                            },
                            {
                                "id": 2 ,
                                "title": "WOMEN Education for Refugees"
                            }
                        ]
                       },
                       {
                        "orderId": 1,
                        "totalUnitsValue": 200,
                        "CPO": 5.5,
                        "PPO": "0.9",
                        "program": [
                            {
                                "id": 2,
                                "title": "Quality Education for Refugees"
                            }
                        ]
                       }
                    ]
                }
               ]

解决方案 #

1. 修正模型关联定义 #

Sequelize返回单个对象还是数组，完全取决于你定义的关联类型：

• belongsTo/hasOne 会返回单个对象
• hasMany/belongsToMany 会返回数组

调整Order与ProgramOrder的关联

在Order模型中，确保定义为一对多关联：

// Order 模型
Order.hasMany(ProgramOrder, {
  as: 'programOrders',
  foreignKey: 'orderId', // 匹配ProgramOrder表中的外键字段
  onDelete: 'CASCADE' // 可选，根据业务需求设置删除规则
});

调整ProgramOrder与Program的关联

如果每个ProgramOrder需要关联多个Program，在ProgramOrder模型中定义一对多关联：

// ProgramOrder 模型
ProgramOrder.hasMany(Program, {
  as: 'program',
  foreignKey: 'programOrderId', // 匹配Program表中的外键字段
  onDelete: 'CASCADE'
});

2. 查询代码无需额外修改 #

关联定义修正后，原查询代码会自动返回数组格式的programOrders和program字段。如果需要允许订单没有关联的ProgramOrder或Program时仍返回数据，可以在include选项中添加required: false（左连接）：

{
  model: ProgramOrder,
  as: 'programOrders',
  attributes: ['orderId', 'totalUnitsValue', 'CPO', 'PPO'],
  required: false, // 可选，允许无关联ProgramOrder的订单返回
  include: 
    {
      model: Program,
      as: 'program',
      attributes: ['title'],
      required: false // 可选，允许无关联Program的ProgramOrder返回
    },
},

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