I was interested in this too, because that's a great example for HasManyThrough associations and for an implementation of edges in a graph-like data structure. I thought you had this schema in mind:
I first tried to solve it using the answer by Gwendal Roué but didn't quite get it to work. I opened a GRDB issue to ask for more help. He answered within hours, solved all my problems and explained the solutions in detail.
This is what i learned:
- Distinct has-many- and has-many-through-associations are necessary to make sure requests treat them independently. Otherwise it would, for example, not be possible to fetch main artists and feature artists using the same request. Without distinct associations the request would combine the filters and only return artists that are defined
main and feature using the same SongArtist (which is not possible, so empty arrays are returned).
- That's why my code defines associations for each
ArtistRelation. My Song struct has five has-many- and an additional five has-many-through-associations.
- When dealing with joined requests it is better to include the entire row of the closure table. It is possible to fetch structs that contain isolated columns only (for example when we are only interested in the
relation) but that would make the code more complex. Gwendal Roué does not recommend this.
- That's why my struct in code example
6 contains an entire SongArtist row although i only need its relation.
The following code contains a full scenario and these fetch requests to test the implementation:
- All songs and the corresponding artists, but without
ArtistRelation info
- All songs and the corresponding artists, grouped in separate arrays according to their
ArtistRelation
- All songs with their number of relationships
- All songs that have no feature artists
- Direct access to the closure table to get all SongArtist rows that define feature artists
- All songs with their artists and their relationships
import GRDB
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]]
}
