Requests for closure tables that contain additional metadata

[mxgzf]

I know how to create many-to-many relationships using closure tables but i have difficulties creating some type of GRDB requests when those closure tables contain additional metadata about the relationship.

Here is an example that relates songs to artists:

An artist can have many songs and songs can have many artists. An ArtistRelation enum describes the relation. For example, a song can have a main artist but also a featured artist.

This is inspired by a recent question on Stack Overflow (by somebody else).

My questions regarding my code below are:

Question 1: What is the best way to query artists related to their ArtistRelation?

In case [1] and [2] you see that i'm already able to make some of the requests. But as soon as i want both "Main Artists" and "Partner Artists" i get empty arrays (see [3]). I suppose that's because i implemented the filter in additional has-many-relationships which is not the right way to do:

static let albumArtists = hasMany(Artist.self, through: songArtists.filter(Column("relation") == ArtistRelation.album), using: SongArtist.artist).forKey("albumArtists")

But the alternative approach in case [4] doesn't work either. In that case the filter Column("relation") doesn't find the relation column of the closure table but tries to access Artist.relation instead which does not exist.

Fails with error 'no such column: artist.relation in "SELECT "artist".* FROM "artist" JOIN "songArtist" ON ("songArtist"."artistId" = "artist"."id") AND ("songArtist"."songId" = ?) WHERE "artist"."relation" = ?"'

Question 2: Are joined requests possible?

Is it possible to create a joined request that combines songs with its corresponding artists and relations? Something that results in the following struct?

struct SongInfo: FetchableRecord, Decodable {
    struct ArtistInfo: Decodable, Hashable {
        var artist: Artist
        var relation: ArtistRelation
    }
    var song: Song
    var artists: Set<ArtistInfo>
}

I tried that as described in The Structure of a Joined Request but didn't get it to work with the relation in the SongArtist table.

My code with the sample data

struct Artist: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    static let songArtists = hasMany(SongArtist.self)
    static let songs = hasMany(Song.self, through: songArtists, using: SongArtist.song)
}

struct Song: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    static let songArtists = hasMany(SongArtist.self)
    static let artists = hasMany(Artist.self, through: songArtists, using: SongArtist.artist)
    static let albumArtists = hasMany(Artist.self, through: songArtists.filter(Column("relation") == ArtistRelation.album), using: SongArtist.artist).forKey("albumArtists")
    static let featureArtists = hasMany(Artist.self, through: songArtists.filter(Column("relation") == ArtistRelation.feature), using: SongArtist.artist).forKey("featureArtists")
    static let mainArtists = hasMany(Artist.self, through: songArtists.filter(Column("relation") == ArtistRelation.main), using: SongArtist.artist).forKey("mainArtists")
    static let partnerArtists = hasMany(Artist.self, through: songArtists.filter(Column("relation") == ArtistRelation.partner), using: SongArtist.artist).forKey("partnerArtists")
    static let partnerArtistsAlternative = artists.filter(Column("relation") == ArtistRelation.partner).forKey("partnerArtistsAlternative")
}

enum ArtistRelation: String, Codable, DatabaseValueConvertible {
    case album
    case feature
    case main
    case partner
}

struct SongArtist: Codable, Hashable, FetchableRecord, PersistableRecord {
    let songId: Int64
    let artistId: Int64
    let relation: ArtistRelation
    static let song = belongsTo(Song.self)
    static let artist = belongsTo(Artist.self)
}

let queue = DatabaseQueue()
try queue.write { db in
    
    try db.create(table: "artist") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "song") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "songArtist") { t in
        t.column("songId", .integer).indexed().references("song")
        t.column("artistId", .integer).indexed().references("artist")
        t.column("relation").notNull()
    }
    
    // Testing real song data from https://music.apple.com/de/album/magnet/1102347168
    
    var missK8 = Artist(name: "Miss K8")
    try missK8.insert(db)
    var mcNolz = Artist(name: "McNolz")
    try mcNolz.insert(db)
    var radicalRedemption = Artist(name: "Radical Redemption")
    try radicalRedemption.insert(db)
    
    var scream = Song(name: "Scream (feat. Mc Nolz)")
    try scream.insert(db)
    try SongArtist(songId: scream.id!, artistId: missK8.id!, relation: .album).insert(db)
    try SongArtist(songId: scream.id!, artistId: mcNolz.id!, relation: .feature).insert(db)
    try SongArtist(songId: scream.id!, artistId: radicalRedemption.id!, relation: .main).insert(db)
    try SongArtist(songId: scream.id!, artistId: missK8.id!, relation: .partner).insert(db)
    
    var raidersOfRampage = Song(name: "Raiders of Rampage")
    try raidersOfRampage.insert(db)
    try SongArtist(songId: raidersOfRampage.id!, artistId: missK8.id!, relation: .album).insert(db)
    try SongArtist(songId: raidersOfRampage.id!, artistId: missK8.id!, relation: .main).insert(db)
    try SongArtist(songId: raidersOfRampage.id!, artistId: mcNolz.id!, relation: .partner).insert(db)
}

// [1] All songs and the corresponding artists, but without `ArtistRelation` info
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var artists: Set<Artist>
        var description: String { "\(song.name) → artists:[\(artists.map(\.name).joined(separator: ", "))]" }
    }
    let request = Song.including(all: Song.artists)
    print(try SongInfo.fetchAll(db, request))
    // > [Scream (feat. Mc Nolz) → artists:[Miss K8, McNolz, Radical Redemption], Raiders of Rampage → [Miss K8, McNolz]]
}

// [2] All songs and the corresponding main artists
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var mainArtists: Set<Artist>
        var description: String { "\(song.name) → mainArtists:[\(mainArtists.map(\.name).joined(separator: ", "))]" }
    }
    let request = Song.including(all: Song.mainArtists)
    print(try SongInfo.fetchAll(db, request))
    // > [Scream (feat. Mc Nolz) → mainArtists:[Radical Redemption], Raiders of Rampage → mainArtists:[Miss K8]]
}

// [3] All songs and the corresponding artists, grouped in separate arrays according to their `ArtistRelation`
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var mainArtists: Set<Artist>
        var partnerArtists: Set<Artist>
        var description: String { "\(song.name) → mainArtists:[\(mainArtists.map(\.name).joined(separator: ", "))], partnerArtists:[\(partnerArtists.map(\.name).joined(separator: ", "))]" }
    }
    let request = Song
        .including(all: Song.mainArtists)
        .including(all: Song.partnerArtists)
    print(try SongInfo.fetchAll(db, request))
    // Expected: [Scream (feat. Mc Nolz) → mainArtists:[Radical Redemption], partnerArtists:[Miss K8], Raiders of Rampage → mainArtists:[Miss K8], partnerArtists:[McNolz]]
    // Got: [Scream (feat. Mc Nolz) → mainArtists:[], partnerArtists:[], Raiders of Rampage → mainArtists:[], partnerArtists:[]]
    // Question: Why are all arrays empty, but [2] works?
}

// [4] All songs and the corresponding partner artists
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var partnerArtistsAlternative: Set<Artist>
        var description: String { "\(song.name) → partnerArtistsAlternative:[\(partnerArtistsAlternative.map(\.name).joined(separator: ", "))]" }
    }
    let request = Song.including(all: Song.partnerArtistsAlternative)
    print(try SongInfo.fetchAll(db, request))
    // Fails with error 'no such column: artist.relation in "SELECT "artist".* FROM "artist" JOIN "songArtist" ON ("songArtist"."artistId" = "artist"."id") AND ("songArtist"."songId" = ?) WHERE "artist"."relation" = ?"'
}

// [5] Alternative: Direct access to the closure table works as expected
try queue.read { db in
    struct SongArtistInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var artist: Artist
        var relation: ArtistRelation
        var description: String { "\(song.name) → \(relation):\(artist.name)" }
    }
    let request = SongArtist
        .including(required: SongArtist.song)
        .including(required: SongArtist.artist)
        .filter(Column("relation") == ArtistRelation.feature)
    print(try SongArtistInfo.fetchAll(db, request))
    // > [Scream (feat. Mc Nolz) → feature:McNolz]
}

Thank you for help!

[groue]

Hello @mxgzf,

This is inspired by a recent question on Stack Overflow (by somebody else).

This question was cross-posted as an issue here: #1063.

Thanks for your sample code, I'll try to give advice shortly.

[groue]

Question 1

First, the fix for [3]:

struct Song: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    static let songArtists = hasMany(SongArtist.self)
    static let artists = hasMany(Artist.self, through: songArtists, using: SongArtist.artist)
    private static func artists(forRelation relation: ArtistRelation) -> HasManyThroughAssociation<Song, Artist> {
        hasMany(
            Artist.self,
            through: songArtists
                .filter(Column("relation") == relation)
                .forKey("\(relation.rawValue)SongArtists"), // was missing
            using: SongArtist.artist)
            .forKey("\(relation.rawValue)Artists")
    }
    static let albumArtists = artists(forRelation: .album)
    static let featureArtists = artists(forRelation: .feature)
    static let mainArtists = artists(forRelation: .main)
    static let partnerArtists = artists(forRelation: .partner)
}

Now I'm looking for the best way to explain why the middle songArtists relation also needs a specific key. Please hold on.

[groue]

Question 1

First, I'm happy that you use association keys. You know that those keys help decoding complex requests into fetchable record properties:

// Default key
struct SongInfo: FetchableRecord, Decodable {
    var song: Song
    var artists: Set<Artist>
}
let songInfos = try Song
    .including(all: Song.artists)
    .asRequest(of: SongInfo.self)
    .fetchAll(db)

// Custom key
struct SongInfo: FetchableRecord, Decodable {
    var song: Song
    var customArtists: Set<Artist> // custom property name
}
let songInfos = try Song
    .including(all: Song.artists.forKey("customArtists") // custom key
    .asRequest(of: SongInfo.self)
    .fetchAll(db)

Now, association keys have another role, which is to split associations apart. This is more subtle, but my goal is to help you understand. This is where your sample code was initially failing, so please follow me.

Usually, you don't quite have to think about it. For example, let's say we need to associate songs with their artists, but split dead and alive ones. Since we need two distinct properties in SongInfo, deadArtists and aliveArtists, we also use two distinct association keys:

struct SongInfo: FetchableRecord, Decodable {
    var song: Song
    var deadArtists: Set<Artist>
    var aliveArtists: Set<Artist>
}
let songInfos = try Song
    .including(all: Song.artists.filter(...).forKey("deadArtists")
    .including(all: Song.artists.filter(...).forKey("aliveArtists")
    .asRequest(of: SongInfo.self)
    .fetchAll(db)

The two distinct keys not only feed two distinct SongInfo properties, but they also tell GRDB to consider those two sets of artists as independent. This is how you end up with distinct sets.

In this example, your will for two distinct property names has done the necessary setup.

---

Let's now see an example where things can turn bad. Let's fetch songs, and for each song, count main and partner artists. Couting the Song.songArtists association is enough, we do not have to consider Song.artists for this request:

// BAD: it looks like songs have no artists at all!
let rows = try Song
    .annotated(with: Song.songArtists
                .filter(Column("relation") == ArtistRelation.main)
                .count)
    .annotated(with: Song.songArtists
                .filter(Column("relation") == ArtistRelation.partner)
                .count)
    .asRequest(of: Row.self)
    .fetchAll(db)
for row in rows { print(row) }
// > [id:1 name:"Scream (feat. Mc Nolz)" songArtistCount:0 songArtistCount:0]
// > [id:2 name:"Raiders of Rampage" songArtistCount:0 songArtistCount:0]

The reason why this request does not provide the correct results are the rules about Refining Association Requests. In particular:

When you join or include an association several times, with the same association key, [...] all filters are applied.

In the above request, the association with key "songArtists" is used twice, with two distinct filters. Because the same association key is used, the association is not split apart. All filters are applied, and songArtist has to be both "main" and "partner", which never happens. And that's why we get wrong results.

The fix is to make sure that the Song.songArtists association is split in two distinct associations that are considered independently. And for that we need distinct association keys:

// CORRECT
let rows = try Song
    .annotated(with: Song.songArtists
                .filter(Column("relation") == ArtistRelation.main)
                .forKey("mainSongArtists")
                .count)
    .annotated(with: Song.songArtists
                .filter(Column("relation") == ArtistRelation.partner)
                .forKey("partnerSongArtists")
                .count)
    .asRequest(of: Row.self)
    .fetchAll(db)
for row in rows { print(row) }
// > [id:1 name:"Scream (feat. Mc Nolz)" mainSongArtistCount:1 partnerSongArtistCount:1]
// > [id:2 name:"Raiders of Rampage" mainSongArtistCount:1 partnerSongArtistCount:1]

Since this becomes verbose, I recommend defining an association method that encapsulates this behavior:

// BETTER
extension Song {
    static let songArtists = hasMany(SongArtist.self)
    static func songArtists(forRelation relation: ArtistRelation) -> HasManyAssociation<Song, SongArtist> {
        songArtists
            .filter(Column("relation") == relation)
            .forKey("\(relation.rawValue)SongArtists")
    }
}

let rows = try Song
    .annotated(with: Song.songArtists(forRelation: .main).count)
    .annotated(with: Song.songArtists(forRelation: .partner).count)
    .asRequest(of: Row.self)
    .fetchAll(db)
for row in rows { print(row) }
// > [id:1 name:"Scream (feat. Mc Nolz)" mainSongArtistCount:1 partnerSongArtistCount:1]
// > [id:2 name:"Raiders of Rampage" mainSongArtistCount:1 partnerSongArtistCount:1]

---

Now we can explain why you could not fetch both main and partner artists.

The Song.artists HasManyThrough association (key "artists") is based on the Song.songArtists HasMany association (key "songArtists").

But the Song.mainArtists and Song.partnerArtists are also based on the same Song.songArtists association with key "songArtists"!

When a request involves both Song.mainArtists and Song.partnerArtists, the Song.songArtists association with key "songArtists" is used twice, with two different and conflicting relation filters, just as in our counting example above.

To fix this, the distinct Song.mainArtists and Song.partnerArtists associations should be based on distinct associations to SongArtist.

And this gives:

extension Song {
    static func artists(forRelation relation: ArtistRelation) -> HasManyThroughAssociation<Song, Artist> {
        hasMany(
            Artist.self,
            through: songArtists(forRelation: relation), // <-
            using: SongArtist.artist)
            .forKey("\(relation.rawValue)Artists")
    }
    static let albumArtists = artists(forRelation: .album)
    static let featureArtists = artists(forRelation: .feature)
    static let mainArtists = artists(forRelation: .main)
    static let partnerArtists = artists(forRelation: .partner)
}

Now you can write requests that involve the distinct mainArtists and partnerArtists associations, and fetch correct results. To do so, the two distinct hasManyThrough associations had to be defined on two distinct associations to SongArtists as well.

---

I hope this explanation had you better understand the roles of association keys, and how you can use them for your own benefit:

1. Use association keys in order to decode associated values in the desired property names.
2. Use association keys in order to split distinct associations apart, when the rules for Refining Association Requests would provide wrong results.
3. Although invisible in requests, the same rules apply to the middle association involved in the creation of a HasManyThrough or HasOneThrough association. Splitting HasManyThrough or HasOneThrough according to this middle association requires splitting this middle association itself.

Maybe future GRDB versions will improve this area of the definition of requests. I know this is not easy, and I thank you for asking your question. User input is invaluable in such a context.

[groue]

Question 2: Are joined requests possible?

Is it possible to create a joined request that combines songs with its corresponding artists and relations? Something that results in the following struct?

struct SongInfo: FetchableRecord, Decodable {
    struct ArtistInfo: Decodable, Hashable {
        var artist: Artist
        var relation: ArtistRelation
    }
    var song: Song
    var artists: Set<ArtistInfo>
}

I tried that as described in The Structure of a Joined Request but didn't get it to work with the relation in the SongArtist table.

The structure of a joined request chapter tells you how to compose complex records out of whole records. Fetching isolated columns (relation) instead of whole records in such joined request is possible, but the Swift code becomes complex. I currently would not advise doing so unless you have a problem that justifies the pain.

Meanwhile, this will work:

try queue.read { db in
    struct SongInfo: Decodable, FetchableRecord {
        struct ArtistInfo: Decodable, Hashable {
            var songArtist: SongArtist
            var artist: Artist
            // var relation: ArtistRelation { songArtist.relation }
        }
        var song: Song
        var artists: Set<ArtistInfo>
    }
    
    let songInfos = try Song
        .including(all: Song.songArtists
                    .including(required: SongArtist.artist)
                    .forKey("artists"))
        .asRequest(of: SongInfo.self)
        .fetchAll(db)
    print(songInfos)
}

[mxgzf]

Thank you very much for your fantastic support and the detailed explanations.

I was able to solve all my issues.

Here is my updated code with the following changes:

• I added distinct has-many- and has-many-through-associations as recommended. A comment references your explanation: "Distinct has-many-associations are necessary to make sure requests treat them independently. See Requests for closure tables that contain additional metadata #1068 (comment) for more information."
• I added the joined request struct that contains the entire SongArtist row. A comment references your explanation: "It is possible to fetch structs that only contain relation as an isolated column but that would make the code more complex. It is easier to fetch the entire SongArtist row and get the relation from there. See Requests for closure tables that contain additional metadata #1068 (comment) for more information."
• I added a comment explaining why i don't define primary keys in the closure table: "We do not define primary keys here using t.primaryKey(["songId", "artistId"]) because we allow multiple SongArtist rows with the same id combination, e.g. when the album artist is also the main artist of a song. See How to create Many-to-many association? #1063 (comment) for an example that defines primary keys for a closure table."
• I added two examples that use .annotated() and .having()

struct Artist: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    static let songArtists = hasMany(SongArtist.self)
    static let songs = hasMany(Song.self, through: songArtists, using: SongArtist.song)
}

struct Song: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    
    // Distinct has-many-associations are necessary to make sure requests treat them independently. See https://github.com/groue/GRDB.swift/issues/1068#issuecomment-927801968 for more information.
    static let songArtists = hasMany(SongArtist.self)
    static func songArtists(forRelation relation: ArtistRelation) -> HasManyAssociation<Song, SongArtist> {
        songArtists
            .filter(Column("relation") == relation)
            .forKey("\(relation.rawValue)SongArtists")
    }
    static let albumSongArtists = songArtists(forRelation: .album)
    static let featureSongArtists = songArtists(forRelation: .feature)
    static let mainSongArtists = songArtists(forRelation: .main)
    static let partnerSongArtists = songArtists(forRelation: .partner)
    
    // Distinct has-many-through-associations are necessary to make sure requests treat them independently. See https://github.com/groue/GRDB.swift/issues/1068#issuecomment-927801968 for more information.
    static let artists = hasMany(Artist.self, through: songArtists, using: SongArtist.artist)
    static func artists(forRelation relation: ArtistRelation) -> HasManyThroughAssociation<Song, Artist> {
        hasMany(
            Artist.self,
            through: songArtists(forRelation: relation),
            using: SongArtist.artist)
            .forKey("\(relation.rawValue)Artists")
    }
    static let albumArtists = artists(forRelation: .album)
    static let featureArtists = artists(forRelation: .feature)
    static let mainArtists = artists(forRelation: .main)
    static let partnerArtists = artists(forRelation: .partner)
}

enum ArtistRelation: String, Codable, DatabaseValueConvertible {
    case album
    case feature
    case main
    case partner
}

struct SongArtist: Codable, Hashable, FetchableRecord, PersistableRecord {
    let songId: Int64
    let artistId: Int64
    let relation: ArtistRelation
    static let song = belongsTo(Song.self)
    static let artist = belongsTo(Artist.self)
}

let queue = DatabaseQueue()
try queue.write { db in
    
    try db.create(table: "artist") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "song") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "songArtist") { t in
        t.column("songId", .integer).notNull().indexed().references("song")
        t.column("artistId", .integer).notNull().indexed().references("artist")
        t.column("relation").notNull()
        // We do not define primary keys here using `t.primaryKey(["songId", "artistId"])` because we allow multiple `SongArtist` rows with the same id combination, e.g. when the album artist is also the main artist of a song. See https://github.com/groue/GRDB.swift/issues/1063#issuecomment-925735039 for an example that defines primary keys for a closure table.
    }
    
    // Testing real song data from https://music.apple.com/de/album/magnet/1102347168
    
    var missK8 = Artist(name: "Miss K8")
    try missK8.insert(db)
    var mcNolz = Artist(name: "McNolz")
    try mcNolz.insert(db)
    var radicalRedemption = Artist(name: "Radical Redemption")
    try radicalRedemption.insert(db)
    
    var scream = Song(name: "Scream (feat. Mc Nolz)")
    try scream.insert(db)
    try SongArtist(songId: scream.id!, artistId: missK8.id!, relation: .album).insert(db)
    try SongArtist(songId: scream.id!, artistId: mcNolz.id!, relation: .feature).insert(db)
    try SongArtist(songId: scream.id!, artistId: radicalRedemption.id!, relation: .main).insert(db)
    try SongArtist(songId: scream.id!, artistId: missK8.id!, relation: .partner).insert(db)
    
    var raidersOfRampage = Song(name: "Raiders of Rampage")
    try raidersOfRampage.insert(db)
    try SongArtist(songId: raidersOfRampage.id!, artistId: missK8.id!, relation: .album).insert(db)
    try SongArtist(songId: raidersOfRampage.id!, artistId: missK8.id!, relation: .main).insert(db)
    try SongArtist(songId: raidersOfRampage.id!, artistId: mcNolz.id!, relation: .partner).insert(db)
}

// 1: All songs and the corresponding artists, but without `ArtistRelation` info
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var artists: Set<Artist>
        var description: String { "\(song.name) → artists:[\(artists.map(\.name).joined(separator: ", "))]" }
    }
    let request = Song.including(all: Song.artists)
    let result = try SongInfo.fetchAll(db, request)
    print("1: \(result)")
    // > 1: [Scream (feat. Mc Nolz) → artists:[Radical Redemption, McNolz, Miss K8], Raiders of Rampage → artists:[Miss K8, McNolz]]
}

// 2: All songs and the corresponding artists, grouped in separate arrays according to their `ArtistRelation`
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var albumArtists: Set<Artist>
        var featureArtists: Set<Artist>
        var mainArtists: Set<Artist>
        var partnerArtists: Set<Artist>
        var description: String { "\(song.name) → albumArtists:\(albumArtists.map(\.name)), featureArtists:\(featureArtists.map(\.name)), mainArtists:\(mainArtists.map(\.name)), partnerArtists:\(partnerArtists.map(\.name))" }
    }
    let request = Song
        .including(all: Song.albumArtists)
        .including(all: Song.featureArtists)
        .including(all: Song.mainArtists)
        .including(all: Song.partnerArtists)
    let result = try SongInfo.fetchAll(db, request)
    print("2: \(result)")
    // > 2: [Scream (feat. Mc Nolz) → albumArtists:["Miss K8"], featureArtists:["McNolz"], mainArtists:["Radical Redemption"], partnerArtists:["Miss K8"], Raiders of Rampage → albumArtists:["Miss K8"], featureArtists:[], mainArtists:["Miss K8"], partnerArtists:["McNolz"]]
}

// 3: All songs with their number of relationships
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var albumSongArtistCount: Int
        var featureSongArtistCount: Int
        var mainSongArtistCount: Int
        var partnerSongArtistCount: Int
        var description: String { "\(song.name) → album:\(albumSongArtistCount), feature:\(featureSongArtistCount), main:\(mainSongArtistCount), partner:\(partnerSongArtistCount)" }
    }
    let result = try Song
        .annotated(with: Song.albumSongArtists.count)
        .annotated(with: Song.featureSongArtists.count)
        .annotated(with: Song.mainSongArtists.count)
        .annotated(with: Song.partnerSongArtists.count)
        .asRequest(of: SongInfo.self)
        .fetchAll(db)
    print("3: \(result)")
    // > 3: [Scream (feat. Mc Nolz) → album:1, feature:1, main:1, partner:1, Raiders of Rampage → album:1, feature:0, main:1, partner:1]
}

// 4: All songs that have no feature artists
try queue.read { db in
    let result = try Song
        .having(Song.featureArtists.isEmpty)
        .fetchAll(db)
    print("4: \(result.map(\.name))")
    // > 4: ["Raiders of Rampage"]
}

// 5: Direct access to the closure table to get all SongArtist rows that define feature artists
try queue.read { db in
    struct SongArtistInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var artist: Artist
        var relation: ArtistRelation
        var description: String { "\(song.name) → \(relation):\(artist.name)" }
    }
    let request = SongArtist
        .including(required: SongArtist.song)
        .including(required: SongArtist.artist)
        .filter(Column("relation") == ArtistRelation.feature)
    let result = try SongArtistInfo.fetchAll(db, request)
    print("5: \(result)")
    // > 5: [Scream (feat. Mc Nolz) → feature:McNolz]
}

// 6: All songs with their artists and their relationships
try queue.read { db in
    // It is possible to fetch structs that only contain `relation` as an isolated column but that would make the code more complex. It is easier to fetch the entire `SongArtist` row and get the relation from there. See https://github.com/groue/GRDB.swift/issues/1068#issuecomment-927815515 for more information.
    struct SongInfo: Decodable, FetchableRecord, CustomStringConvertible {
        struct ArtistInfo: Decodable, Hashable, CustomStringConvertible {
            var songArtist: SongArtist
            var artist: Artist
            var description: String { "\(songArtist.relation):\(artist.name)" }
        }
        var song: Song
        var artists: Set<ArtistInfo>
        var description: String { "\(song.name) → \(artists)" }
    }
    let result = try Song
        .including(all: Song.songArtists
                    .including(required: SongArtist.artist)
                    .forKey("artists"))
        .asRequest(of: SongInfo.self)
        .fetchAll(db)
    print("6: \(result)")
    // > 6: [Scream (feat. Mc Nolz) → [feature:McNolz, main:Radical Redemption, album:Miss K8, partner:Miss K8], Raiders of Rampage → [album:Miss K8, main:Miss K8, partner:McNolz]]
}