Swift使用GRDB时多对多关联同一实体分多角色如何建模？

最优解决方案：在多对多联结表中新增「艺人角色」字段 #

你只需要在原有的ArtistSong联结表中新增一个角色枚举字段，用来区分同一条关联对应的艺人类型，之后基于这个字段做关联筛选即可，不需要额外新增表或者修改Artist、Song的基础表结构。

具体实现步骤

1. 先定义艺人角色枚举，适配GRDB的存储要求

enum ArtistRole: String, Codable, DatabaseValueConvertible {
    case main // 主艺人
    case featured // 特邀艺人
    case album // 专辑艺人
}

2. 修改ArtistSong联结表结构，新增角色字段

struct ArtistSong: TableRecord {
    let artistId: Int
    let songId: Int
    let role: ArtistRole // 新增的角色字段
    
    static let artist = belongsTo(Artist.self)
    static let song = belongsTo(Song.self)
    
    // 新增便捷筛选方法，后续关联会用到
    static func filtered(by role: ArtistRole) -> QueryInterfaceRequest<ArtistSong> {
        filter(Column("role") == role)
    }
}

3. 修改Song模型，分别定义三类艺人的关联

struct Song: TableRecord {
    let id: Int
    let name: String
    // 其他原有字段...
    
    static let artistSong = hasMany(ArtistSong.self)
    // 原有的全量艺人关联可以保留
    static let artists = hasMany(Artist.self, through: artistSong, using: ArtistSong.artist)
    
    // 新增三类分类关联
    static let mainArtists = hasMany(
        Artist.self,
        through: artistSong.filtered(by: .main),
        using: ArtistSong.artist
    )
    static let featuredArtists = hasMany(
        Artist.self,
        through: artistSong.filtered(by: .featured),
        using: ArtistSong.artist
    )
    static let albumArtists = hasMany(
        Artist.self,
        through: artistSong.filtered(by: .album),
        using: ArtistSong.artist
    )
}

4. （可选）给Artist模型新增反向分类关联

struct Artist: TableRecord {
    let id: Int
    let name: String
    // 其他原有字段...
    
    static let artistSong = hasMany(ArtistSong.self)
    // 原有全量歌曲关联保留
    static let songs = hasMany(Song.self, through: artistSong, using: ArtistSong.song)
    
    // 新增反向分类关联
    static let mainSongs = hasMany(
        Song.self,
        through: artistSong.filtered(by: .main),
        using: ArtistSong.song
    )
    static let featuredSongs = hasMany(
        Song.self,
        through: artistSong.filtered(by: .featured),
        using: ArtistSong.song
    )
    static let albumSongs = hasMany(
        Song.self,
        through: artistSong.filtered(by: .album),
        using: ArtistSong.song
    )
}

使用示例

查询某首歌的所有特邀艺人时可以直接调用关联：

let song: Song = ...
let featuredArtists = try! dbQueue.read { db in
    try song.featuredArtists.fetchAll(db)
}

插入关联时也只需要指定对应role即可：

let artistSong = ArtistSong(artistId: 1, songId: 10, role: .main)
try! dbQueue.write { db in
    try artistSong.insert(db)
}

这个方案的扩展性也很强，后续如果新增其他艺人角色，只需要给ArtistRole加枚举值即可，不需要调整表结构。

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