Sequelize如何将关联模型字段平级嵌入关联子模型属性？

模型与关联关系 #

数据表结构 #

User
|userid|fullname   |
|1     |Joe Klan   |
|2     |Tim Thomson|
|3     |Qasim Ali  |

Task
|taskid|taskname|
|1     |TSP     |
|2     |GS1     |
|3     |GS2     |
|4     |GS3     |

Record
|recordid|userid|taskid|units|boxes|orderdate |
|1       |1     |2     |23   |43   |2023-03-04|
|2       |1     |1     |18   |25   |2023-03-04|
|3       |1     |3     |12   |15   |2023-03-04|
|4       |1     |2     |26   |58   |2023-03-04|
|5       |1     |1     |23   |76   |2023-03-04|
|6       |2     |4     |34   |19   |2023-03-04|
|7       |2     |2     |51   |88   |2023-03-04|
|8       |2     |3     |15   |73   |2023-03-04|

Sequelize关联配置 #

User.hasMany(Record,{foreignKey: 'userid'});
Record.belongsTo(Task, { foreignKey: "taskid" });

问题场景 #

现有查询会将Task的taskname字段嵌套在Record的Task子对象中，不符合平级返回的需求：

当前查询代码

const result = await User.findAll({
  where: { userid: id },
  attributes: ["userid", "fullname"],
  include: [
    {
      model: Record,
      attributes: ["recordid", "taskid", "units", "boxes"],
      include: [{ model: Task, attributes: ["taskname"] }],
    },
  ],
});

当前返回结果

[
  {
    userid: 1,
    fullname: "Joe Klan",
    Records: [
      {
        recordid: 1,
        taskid: 3,
        units: 23,
        boxes: 43,
        Task: {
          taskname: "GS2",
        },
      },
    ],
  },
]

尝试直接在Record的attributes中写Task.taskname会触发错误：Unknown column 'Records.Task.taskname' in 'field list'，需要找到正确的实现方式。

可行解决方案 #

方案1：查询时手动关联字段并指定别名 #

通过Sequelize.col()关联Task的字段，同时在Record的属性列表中定义别名，实现平级返回：

const result = await User.findAll({
  where: { userid: id },
  attributes: ["userid", "fullname"],
  include: [
    {
      model: Record,
      attributes: [
        "recordid", 
        "taskid", 
        "units", 
        "boxes",
        [Sequelize.col('Task.taskname'), 'taskname']
      ],
      include: [{ 
        model: Task, 
        attributes: [], // 不返回嵌套的Task对象
        required: true // 强制关联，可根据需求改为false（LEFT JOIN）
      }],
    },
  ],
});

方案2：查询后手动处理结果 #

如果不想修改查询逻辑，可以在获取结果后遍历处理，将嵌套字段提取到平级：

// 原查询代码不变
const result = await User.findAll({
  where: { userid: id },
  attributes: ["userid", "fullname"],
  include: [
    {
      model: Record,
      attributes: ["recordid", "taskid", "units", "boxes"],
      include: [{ model: Task, attributes: ["taskname"] }],
    },
  ],
});

// 处理结果结构
result.forEach(user => {
  user.Records.forEach(record => {
    record.taskname = record.Task.taskname;
    delete record.Task;
  });
});

方案3：在Record模型中定义虚拟字段 #

在Record模型添加虚拟字段，自动关联Task的taskname，查询时直接引用即可：

// Record模型定义中新增字段
taskname: {
  type: DataTypes.VIRTUAL,
  get() {
    return this.Task?.taskname;
  }
}

查询代码

const result = await User.findAll({
  where: { userid: id },
  attributes: ["userid", "fullname"],
  include: [
    {
      model: Record,
      attributes: ["recordid", "taskid", "units", "boxes", "taskname"],
      include: [{ model: Task, attributes: [] }],
    },
  ],
});

以上三种方案均可实现将taskname平级嵌入Record的返回属性中，可根据业务场景选择合适的方式。

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