Node.js中Sequelize查询：获取含关联客户的交易与支付的用户数据

问题背景 #

涉及四张表：User、Customer、Transaction、Payment，表间关联关系为：User拥有多个Customer，每个Customer拥有多个Transaction和Payment。

当前使用的Sequelize查询可正常运行，但返回结果中Customer是顶层数组，需要将Transactions和Payments设为顶层数组，且每个交易、支付对象包含其关联的Customer信息。

当前查询代码 #

const user = await User.findByPk(userId, {
  include: [
    {
      model: Customer,
      include: [
        { model: Transaction, include: Customer },
        { model: Payment, include: Customer }
      ]
    },
    { model: Expense }
  ]
});

当前返回结构 #

{
  "id": 1,
  "name": "John Doe",
  "Customers": [
    {
      "id": 1,
      "name": "Customer A",
      "email": "customer@example.com",
      "Transaction": [],
      "Payment": []
    },
    {
      "id": 2,
      "name": "Customer B",
      "email": "customer@example.com",
      "Transaction": [],
      "Payment": []
    }
  ]
}

期望返回结构 #

{
  "id": 1,
  "name": "John Doe",
  "Transactions": [
    {
      "id": 1,
      "quantity": 10,
      "Customer": {
        "id": 1,
        "name": "Customer A",
        "email": "customer@example.com"
      }
    }
  ],
  "Payments": [
    {
      "id": 1,
      "amount": 100,
      "Customer": {
        "id": 2,
        "name": "Customer B",
        "email": "another@example.com"
      }
    }
  ],
  "Expenses": []
}

解决方案 #

方案1：通过模型关联直接查询（推荐） #

先在User模型中建立与Transaction、Payment的间接关联：

// User 模型定义
User.hasMany(Customer);
Customer.hasMany(Transaction);
// 建立 User 到 Transaction 的间接关联
User.hasMany(Transaction, { through: Customer, as: 'Transactions' });

// 同理配置 Payment
Customer.hasMany(Payment);
User.hasMany(Payment, { through: Customer, as: 'Payments' });

然后修改查询代码，直接将Transactions、Payments作为顶层关联查询：

const user = await User.findByPk(userId, {
  include: [
    {
      model: Transaction,
      as: 'Transactions',
      include: [{ model: Customer }]
    },
    {
      model: Payment,
      as: 'Payments',
      include: [{ model: Customer }]
    },
    { model: Expense }
  ]
});

方案2：查询后手动整理数据 #

如果不想修改模型关联，可以在查询后手动处理返回结果，提取Transactions和Payments到顶层：

const user = await User.findByPk(userId, {
  include: [
    {
      model: Customer,
      include: [Transaction, Payment]
    },
    { model: Expense }
  ]
});

// 转换为JSON对象便于修改
const userJson = user.toJSON();

// 初始化顶层的Transactions和Payments数组
const formattedUser = {
  ...userJson,
  Transactions: [],
  Payments: []
};

// 遍历所有Customer，提取关联的交易和支付
formattedUser.Customers.forEach(customer => {
  // 处理交易，添加Customer信息
  formattedUser.Transactions.push(
    ...customer.Transaction.map(transaction => ({
      ...transaction,
      Customer: {
        id: customer.id,
        name: customer.name,
        email: customer.email
      }
    }))
  );
  // 处理支付，添加Customer信息
  formattedUser.Payments.push(
    ...customer.Payment.map(payment => ({
      ...payment,
      Customer: {
        id: customer.id,
        name: customer.name,
        email: customer.email
      }
    }))
  );
});

// 删除原有的Customers数组
delete formattedUser.Customers;

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