PostgreSQL多对多关联加载的Group By处理及音乐榜单投票系统数据库设计与查询实现咨询

你的数据库设计完全没问题——通过toplist_song中间表维护Toplist和Song的多对多关联，同时存储每首歌在对应榜单的投票数，这是处理关联关系附带额外属性场景的标准方案，比TypeORM自动生成的无属性中间表更灵活，后续扩展投票统计、排序这类业务需求也很方便。

数据库设计回顾 #

先整理下你给出的TypeORM实体定义：

Toplist 实体 #

export class Toplist extends BaseEntity {
  @Column({ type: 'varchar', nullable: false })
  name: string;

  @ManyToMany((type) => Song)
  @JoinTable({
    name: 'toplist_song',
    joinColumn: { name: 'toplistId', referencedColumnName: 'id' },
    inverseJoinColumn: { name: 'songId', referencedColumnName: 'id' },
  })
  songs: Song[];

  @Column({ type: 'date', nullable: false })
  endDate: Date;
}

ToplistSong 中间表实体 #

@Entity('toplist_song')
export class ToplistSong {
  @PrimaryColumn('int')
  toplistId: number;

  @PrimaryColumn('int')
  songId: number;

  @ManyToOne((type) => Toplist, { nullable: false })
  @JoinColumn()
  toplist: Toplist;

  @ManyToOne((type) => Song, { nullable: false })
  @JoinColumn()
  song: Song;

  @Column({ type: 'int', nullable: false, default: 0 })
  votes: number;
}

PostgreSQL 查询语句实现 #

要返回你需要的嵌套榜单+歌曲结构，我们可以利用PostgreSQL的JSON_AGG和JSON_BUILD_OBJECT函数来聚合数据：

SELECT
  t.id,
  t.name,
  t.end_date AS "endDate",
  JSON_AGG(
    JSON_BUILD_OBJECT(
      'id', s.id,
      'title', s.title,
      'genre', s.genre,
      'votes', ts.votes
    ) ORDER BY s.id
  ) AS songs
FROM toplist t
JOIN toplist_song ts ON t.id = ts.toplist_id
JOIN song s ON ts.song_id = s.id
GROUP BY t.id, t.name, t.end_date
ORDER BY t.id;

语句说明 #

• JOIN toplist_song ts：关联中间表获取每首歌的投票数
• JOIN song s：关联歌曲表获取歌曲基础信息
• JSON_BUILD_OBJECT：构造符合需求的歌曲对象结构，注意字段名映射（比如数据库中的end_date转成驼峰式"endDate"）
• JSON_AGG(...)：将同一榜单下的所有歌曲聚合为JSON数组，ORDER BY s.id保证歌曲顺序稳定

期望输出示例 #

执行上述查询后，会返回完全匹配你需求的JSON结构：

[
  {
    "id": 1,
    "name": "First toplist",
    "endDate": "2022-06-20",
    "songs": [
      { "id": 1, "title": "Test Song 1", "genre": "Hip-Hop", "votes": 0 },
      { "id": 2, "title": "Test Song 2", "genre": "Hip-Hop", "votes": 0 },
      { "id": 3, "title": "Test Song 3", "genre": "Hip-Hop", "votes": 0 }
    ]
  },
  {
    "id": 2,
    "name": "Second toplist",
    "endDate": "2022-10-20",
    "songs": [
      { "id": 4, "title": "Second Test Song 1", "genre": "Jazz", "votes": 0 },
      { "id": 5, "title": "Second Test Song 2", "genre": "Jazz", "votes": 0 },
      { "id": 6, "title": "Second Test Song 3", "genre": "Jazz", "votes": 0 }
    ]
  }
]

可选优化建议 #

• 如果需要支持空榜单（没有关联歌曲的榜单），可以把JOIN改成LEFT JOIN，并通过COALESCE(JSON_AGG(...), '[]'::JSON)保证songs字段始终是数组
• 如果需要按投票数排序歌曲，修改JSON_AGG中的ORDER BY为ts.votes DESC即可

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