Sequelize关联查询问题：如何将多地址合并为数组返回单用户行

User表与UserAddress表通过UserId字段关联，一个用户对应多个地址。当前查询返回多条重复用户数据，每条对应一个地址；期望返回单条用户数据，地址以数组形式呈现。

当前查询结果 #

{
  id: 1,
  first_name: 'Denise',
  last_name: 'Morata',
  middle_name: '',
  address: 'address1'
},
{
  id: 1,
  first_name: 'Denise',
  last_name: 'Morata',
  middle_name: '',
  address: 'address2'
},
{
  id: 1,
  first_name: 'Denise',
  last_name: 'Morata',
  middle_name: '',
  address: 'address3'
}

期望结果 #

{
  id: 1,
  first_name: 'Denise',
  last_name: 'Morata',
  middle_name: '',
  address: [
    'address1',
    'address2',
    'address3'
  ]
}

现有代码 #

let User = await Database.getConnection().models.User;
let UserAddress = await Database.getConnection().models.UserAddress;

User.hasMany(UserAddress, { foreignKey: "UserId" });

let result = await User.findAll({
    attributes: [
      "_id",
      "username",
      "first_name",
      "middle_name",
      "last_name",
      "suffix_name",
      "UserAddress.address",
    ],
    include: [
        { model: UserAddress, attributes:["address"] }
    ],
  raw: true,
});

问题根源在于raw: true配置，它会让Sequelize返回扁平化的原始SQL查询结果，而非关联后的嵌套结构；同时在attributes中直接指定UserAddress.address，会导致关联查询返回多行重复用户数据。

按以下方式修改代码即可实现需求：

1. 移除raw: true，让Sequelize自动生成嵌套的关联数据结构
2. 删除attributes中的"UserAddress.address"，关联模型的属性已在include中配置
3. 手动处理查询结果，将关联的地址列表提取为用户对象的数组字段

修改后的代码：

let User = await Database.getConnection().models.User;
let UserAddress = await Database.getConnection().models.UserAddress;

User.hasMany(UserAddress, { foreignKey: "UserId" });

let result = await User.findAll({
    attributes: [
      "_id",
      "username",
      "first_name",
      "middle_name",
      "last_name",
      "suffix_name",
    ],
    include: [
        { model: UserAddress, attributes:["address"] }
    ],
});

// 格式化结果，将关联地址转为数组
const formattedResult = result.map(user => {
  const userObj = user.toJSON();
  return {
    ...userObj,
    address: userObj.UserAddresses.map(item => item.address)
  };
});

注：Sequelize会自动将hasMany关联的模型实例转为复数形式的属性（如UserAddresses），如果你的关联别名不同，需要对应调整该字段名。

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