TypeOrm+PostgreSQL按postId分组哈希标签为数组的实现求助

需求：查询帖子时，将同一帖子（相同postId）的哈希标签聚合为数组形式返回。

当前代码及返回结果 #

查询代码 #

async findAll(): Promise<Post[]> {
  const posts = this.postRepository
    .createQueryBuilder('posts')
    .select([
      'posts.id',
      'posts.title',
      'posts.createdAt',
      'posts.hits',
      'users.name',
      'hashtags.hashtag',
    ])
    .addSelect((subQuery) => {
      return subQuery
        .select('SUM(likes.postId)')
        .from(PostLike, 'likes')
        .groupBy('likes.postId');
    }, 'likes')
    .leftJoin('posts.user', 'users')
    .leftJoin('posts.hashtags', 'hashtags')
    .leftJoin('posts.postLikes', 'likes')
    // .groupBy('posts.id')
    .getRawMany();
  return posts;
}

当前返回结果 #

[
    {
        "posts_createdAt": "2022-11-15T14:56:54.083Z",
        "posts_id": 41,
        "posts_title": "야호",
        "posts_hits": null,
        "users_name": "chan",
        "hashtags_hashtag": "#야호",
        "likes": null
    },
    {
        "posts_createdAt": "2022-11-15T14:56:54.083Z",
        "posts_id": 41,
        "posts_title": "야호",
        "posts_hits": null,
        "users_name": "chan",
        "hashtags_hashtag": "#여어행",
        "likes": null
    },
    {
        "posts_createdAt": "2022-11-15T14:58:17.661Z",
        "posts_id": 42,
        "posts_title": "여행",
        "posts_hits": null,
        "users_name": "chan",
        "hashtags_hashtag": "#나혼자",
        "likes": null
    },
    {
        "posts_createdAt": "2022-11-15T14:58:17.661Z",
        "posts_id": 42,
        "posts_title": "여행",
        "posts_hits": null,
        "users_name": "chan",
        "hashtags_hashtag": "#제주도",
        "likes": null
    }
]

解决方案 #

方法一：利用PostgreSQL聚合函数array_agg直接查询 #

通过PostgreSQL的array_agg函数在数据库层面直接聚合哈希标签，同时修正点赞数统计的子查询（原查询中SUM(likes.postId)逻辑错误，应改为统计点赞数量）：

async findAll(): Promise<any[]> {
  const posts = this.postRepository
    .createQueryBuilder('posts')
    .select([
      'posts.id',
      'posts.title',
      'posts.createdAt',
      'posts.hits',
      'users.name',
      // 聚合哈希标签为数组，DISTINCT避免重复标签
      'array_agg(DISTINCT hashtags.hashtag) as hashtags',
    ])
    .addSelect((subQuery) => {
      // 子查询关联当前post的id，统计该post的点赞数
      return subQuery
        .select('COUNT(likes.id)')
        .from(PostLike, 'likes')
        .where('likes.postId = posts.id');
    }, 'likesCount')
    .leftJoin('posts.user', 'users')
    .leftJoin('posts.hashtags', 'hashtags')
    // 按非聚合字段分组
    .groupBy('posts.id, posts.title, posts.createdAt, posts.hits, users.name')
    .getRawMany();
  return posts;
}

该方法返回结果示例：

[
    {
        "posts_id": 41,
        "posts_title": "야호",
        "posts_createdAt": "2022-11-15T14:56:54.083Z",
        "posts_hits": null,
        "users_name": "chan",
        "hashtags": ["#야호", "#여어행"],
        "likesCount": 0
    },
    {
        "posts_id": 42,
        "posts_title": "여행",
        "posts_createdAt": "2022-11-15T14:58:17.661Z",
        "posts_hits": null,
        "users_name": "chan",
        "hashtags": ["#나혼자", "#제주도"],
        "likesCount": 0
    }
]

方法二：代码层面合并结果 #

如果不想依赖数据库聚合函数，可以先查询原始数据，再在代码中合并相同postId的条目，收集哈希标签数组：

async findAll(): Promise<any[]> {
  const rawPosts = this.postRepository
    .createQueryBuilder('posts')
    .select([
      'posts.id',
      'posts.title',
      'posts.createdAt',
      'posts.hits',
      'users.name',
      'hashtags.hashtag',
    ])
    .addSelect((subQuery) => {
      return subQuery
        .select('COUNT(likes.id)')
        .from(PostLike, 'likes')
        .where('likes.postId = posts.id');
    }, 'likesCount')
    .leftJoin('posts.user', 'users')
    .leftJoin('posts.hashtags', 'hashtags')
    .getRawMany();

  // 合并相同postId的记录，聚合hashtags为数组
  const groupedPosts = rawPosts.reduce((acc, current) => {
    const existingPost = acc.find(post => post.posts_id === current.posts_id);
    if (existingPost) {
      // 若当前有哈希标签，添加到已有数组（去重可选）
      if (current.hashtags_hashtag && !existingPost.hashtags.includes(current.hashtags_hashtag)) {
        existingPost.hashtags.push(current.hashtags_hashtag);
      }
    } else {
      acc.push({
        ...current,
        hashtags: current.hashtags_hashtag ? [current.hashtags_hashtag] : []
      });
    }
    return acc;
  }, [] as any[]);

  return groupedPosts;
}

两种方法对比 #

• 方法一：性能更优，数据库层面聚合减少数据传输量，适合数据量大的场景；但依赖PostgreSQL特定函数。
• 方法二：逻辑更灵活，不依赖数据库特性，适合需要复杂数据转换的场景；但数据量大时会增加内存和处理开销。

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