如何在Swift中使用GRDB创建多对多的关联？

Question

我试着在歌曲和专辑之间建立一个联系。每首歌可以出现在一个或多个专辑上，每个专辑可以包含一首或多首歌曲。我决定使用GRDB作为我的数据库解决方案，但在这个问题上我陷入了困境。

我尝试过的是:正如文档所示，我创建了一个passport结构，如下所示：

public struct AlbumPassport: TableRecord {
    static let track = belongsTo(SPTTrack.self)
    static let album = belongsTo(SPTAlbum.self)
}

然后在SPTTrack类中：

public static let passports = hasMany(AlbumPassport.self)
public static let albums = hasMany(SPTAlbum.self, through: passports, using: AlbumPassport.album)

在SPTAlbum课程中：

public static let passports = hasMany(AlbumPassport.self)
public static let tracks = hasMany(SPTTrack.self, through: passports, using: AlbumPassport.track)

我在文档中找不到一个很好的例子，说明如何使用这些关联构建请求。在SPTAlbum类中，我添加了linkedTracks属性

public var linkedTracks: QueryInterfaceRequest<SPTTrack> {
    request(for: Self.tracks)
}

然后在我的数据库管理器中：

func fetchTracks(for album: SPTAlbum) -> [SPTTrack] {
    do {
        return try dbQueue.read { db in
            try album.linkedTracks.fetchAll(db)
        }
    } catch {
        print(error)
    }
    return []
}

我搞错了：

SQLite错误1:没有这样的表: albumPassport

这是非常不言自明的，但我不知道应该如何在何处为AlbumPassport结构创建表，以及是否需要采取任何其他步骤来实际填充此表中的相册/曲目连接。

两个SPTTrack/SPTAlbum都有一个名为id的字段，该字段在第一次迁移期间被设置为primaryKey。

Answer 1

你的协会没有问题。所有hasMany()和belongsTo()都是正确的。您所得到的错误告诉我，您的数据库设置有问题(您的问题中没有包含)。

以下是我将如何实现它：

​

​

import GRDB

struct Album: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    static let passports = hasMany(AlbumPassport.self)
    static let tracks = hasMany(Track.self, through: passports, using: AlbumPassport.track)
}

struct Track: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    static let passports = hasMany(AlbumPassport.self)
    static let albums = hasMany(Album.self, through: passports, using: AlbumPassport.album)
}

struct AlbumPassport: Codable, Hashable, FetchableRecord, PersistableRecord {
    let track: Int64
    let album: Int64
    static let track = belongsTo(Track.self)
    static let album = belongsTo(Album.self)
}

let queue = DatabaseQueue()
try queue.write { db in
    
    try db.create(table: "album") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "track") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "albumPassport") { t in
        t.column("track", .integer).notNull().indexed().references("track")
        t.column("album", .integer).notNull().indexed().references("album")
        t.primaryKey(["track", "album"])
    }
    
    // Testing real data from https://music.apple.com/de/artist/yiruma/73406786
    
    var solo = Album(name: "SOLO")
    try solo.insert(db)
    var sometimes = Track(name: "Sometimes Someone")
    try sometimes.insert(db)
    try AlbumPassport(track: sometimes.id!, album: solo.id!).insert(db)
    var destiny = Track(name: "Destiny Of Love")
    try destiny.insert(db)
    try AlbumPassport(track: destiny.id!, album: solo.id!).insert(db)
    
    var bestOf = Album(name: "Best of Yiroma")
    try bestOf.insert(db)
    var poem = Track(name: "Poem")
    try poem.insert(db)
    try AlbumPassport(track: poem.id!, album: bestOf.id!).insert(db)
    var river = Track(name: "River Flows In You")
    try river.insert(db)
    try AlbumPassport(track: river.id!, album: bestOf.id!).insert(db)
}

// Fetch all albums and their tracks
try queue.read { db in
    struct AlbumInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var album: Album
        var tracks: Set<Track>
        var description: String { "\(album.name) → \(tracks.map(\.name))" }
    }
    let request = Album.including(all: Album.tracks)
    let result = try AlbumInfo.fetchAll(db, request)
    print(result)
    // > [SOLO → ["Sometimes Someone", "Destiny Of Love"], Best of Yiroma → ["River Flows In You", "Poem"]]
}

有关另一个代码示例，请参见我的回答 of 多对多关系，其中一个实体具有相同类型的多个属性。。