Sequelize关联查询：获取Avatar对象而非字符串的问题

使用Sequelize查询PostTip模型时，关联了User模型及其关联的AwsAvatar模型。尝试通过sequelize.col将avatar作为对象字段加入查询结果时，得到的是类似'(1,1,https://xxx...)'的字符串，而非期望的包含id、src等属性的结构化Avatar对象。name、email这类单个字段可正常获取，目前仅能通过循环遍历结果调整结构，希望找到无需循环的更优查询方案。

现有查询代码 #

PostTip.findAndCountAll({
      attributes: {
        include: [
          [db.sequelize.col(`"user"->"avatar"`), "avatar"],
          [db.sequelize.col(`"user"."name"`), "name"],
          [db.sequelize.col(`"user"."email"`), "email"],
        ],
      },
      offset: offset,
      limit: limit,
      order: order,
      where: {
        postId: req.params.id,
      },
      include: [
        {
          model: User,
          as: "user",
          attributes: [],
          include: [{ model: AwsAvatar, as: "avatar" }],
        },
      ],
    }).then((result) => {
      console.log(JSON.stringify(result.rows, null, 2));
      const data = getPagingData(result.rows, result.count, query.page, limit);
      return res.send(data);
    });

当前结果 #

{
    "id": 1,
    "userId": 1,
    "postId": 1,
    "tipAmount": 100,
    "createdAt": "2023-01-11T22:10:26.440Z",
    "updatedAt": "2023-01-11T22:10:26.440Z",
    "avatar": "(1,1,https://dh7ieyc6s2dxm.cloudfront.net/avatars/1673514260216_0e69489d-fc95-4ed8-b615-2a76ce43385.webp,\"2023-01-12 09:04:21.223+00\",\"2023-01-12 09:04:21.223+00\")",
    "name": "Test User",
    "email": "test@test"
  }

期望结果 #

{
    "id": 1,
    "userId": 1,
    "postId": 1,
    "tipAmount": 100,
    "createdAt": "2023-01-11T22:10:26.440Z",
    "updatedAt": "2023-01-11T22:10:26.440Z",
    "avatar": {
       "id": 1,
       "userId": 1,
       "src": "https://dh7ieyc6s2dxm.cloudfront.net/avatars/1673514260216_0e69489d-fc95-4ed8-b615-2a76ce5ff385.webp",
       "createdAt": "2023-01-12T09:04:21.223Z",
       "updatedAt": "2023-01-12T09:04:21.223Z"
    },
    "name": "Test User",
    "email": "test@test"
  }

方法1：利用Sequelize对象映射提取嵌套结构 #

无需手动用sequelize.col映射avatar，调整关联配置后，从嵌套结构中提取目标字段：

PostTip.findAndCountAll({
  attributes: {
    include: [
      [db.sequelize.col(`"user"."name"`), "name"],
      [db.sequelize.col(`"user"."email"`), "email"],
    ],
  },
  offset: offset,
  limit: limit,
  order: order,
  where: {
    postId: req.params.id,
  },
  include: [
    {
      model: User,
      as: "user",
      attributes: [],
      include: [{ 
        model: AwsAvatar, 
        as: "avatar",
        attributes: ["id", "userId", "src", "createdAt", "updatedAt"]
      }],
    },
  ],
  raw: false
}).then((result) => {
  const formattedRows = result.rows.map(row => {
    const { user, ...rest } = row.toJSON();
    return {
      ...rest,
      avatar: user?.avatar || null,
      name: row.name,
      email: row.email
    };
  });
  const data = getPagingData(formattedRows, result.count, query.page, limit);
  return res.send(data);
});

方法2：数据库层面解析为JSON（PostgreSQL适用） #

如果使用PostgreSQL，通过cast函数将字符串结果转为JSON对象：

PostTip.findAndCountAll({
  attributes: {
    include: [
      [db.sequelize.fn('cast', db.sequelize.col(`"user"->"avatar"`), 'json'), "avatar"],
      [db.sequelize.col(`"user"."name"`), "name"],
      [db.sequelize.col(`"user"."email"`), "email"],
    ],
  },
  offset: offset,
  limit: limit,
  order: order,
  where: {
    postId: req.params.id,
  },
  include: [
    {
      model: User,
      as: "user",
      attributes: [],
      include: [{ model: AwsAvatar, as: "avatar" }],
    },
  ],
  raw: true
}).then((result) => {
  const data = getPagingData(result.rows, result.count, query.page, limit);
  return res.send(data);
});

方法3：启用nest模式自动构建嵌套对象 #

通过include中的属性映射，配合nest: true让Sequelize自动生成结构化对象：

PostTip.findAndCountAll({
  offset: offset,
  limit: limit,
  order: order,
  where: {
    postId: req.params.id,
  },
  include: [
    {
      model: User,
      as: "user",
      attributes: [
        ["name", "name"],
        ["email", "email"],
        [db.sequelize.col('avatar.id'), 'avatar.id'],
        [db.sequelize.col('avatar.userId'), 'avatar.userId'],
        [db.sequelize.col('avatar.src'), 'avatar.src'],
        [db.sequelize.col('avatar.createdAt'), 'avatar.createdAt'],
        [db.sequelize.col('avatar.updatedAt'), 'avatar.updatedAt'],
      ],
      include: [{ 
        model: AwsAvatar, 
        as: "avatar",
        attributes: []
      }],
    },
  ],
  raw: true,
  nest: true
}).then((result) => {
  const data = getPagingData(result.rows, result.count, query.page, limit);
  return res.send(data);
});

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