How to query a table with a 1-1 association on iOS using GRDB?

Question

In my iOS project, I use GRDB to manage a SQLite database. I try to query a table with a 1-1 association.

In my database, let's say I have 2 tables:

Selections
----------
- selectionId (primary key)
- position
- idSong (foreign key)

Songs
-----
- songId (primary key)
- name
- artist

Those two entities are linked with a 1-1 association, thanks to idSong.

Here is what I tried:

the Selection entity:

class Selection : Record
{
    var selectionId: Int64?
    var position: Int?
    var idSong: Int64?

    static let song = hasOne(Song.self, key: "selectionId", using: ForeignKey(["songId"]))
    var song: QueryInterfaceRequest<Song> {
        request(for: Selection.song)
    }

    // ...
}

the Song entity:

class Song : Record
{
    var songId: Int64?
    var name: String?
    var artist: String?

    static let selection = belongsTo(Selection.self)
    var selection: QueryInterfaceRequest<Selection> {
        request(for: Song.selection)
    }

    // ...
}

a SelectionSong struct:

struct SelectionSong : FetchableRecord
{
    let selection: Selection
    let song: Song
    
    
    init(row: Row)
    {
        selection = row[Selection.databaseTableName]
        song = row[Song.databaseTableName]
    }
}

here is how I created the two tables:

    // Selections:
    try db.create(table: Selection.databaseTableName) { table in
        
        table.autoIncrementedPrimaryKey("selectionId")
        table.column("position", .integer).notNull()
        table.column("idSong", .integer)
            .notNull()
            .indexed()
            .references(Song.databaseTableName, onDelete: .cascade)
    }

    // Songs:
    try db.create(table: Song.databaseTableName) { table in
        
        table.autoIncrementedPrimaryKey("songId")
        table.column("name", .text).notNull()
        table.column("artist", .text).notNull()
    }

and here is how I try to get a list of SelectionSong, so I can get a list of Selection, and for each Selection, the associated Song:

    let request = Selection.including(optional: Selection.song)
    let list = try SelectionSong.fetchAll(db, request)

but then I get the following error: missing scope 'song'.

So how can I query my Selection table, so I can get a list of SelectionSong (a Selection with the associated Song)?

Thanks.

score 1 · Accepted Answer · answered Jan 29 '21 at 20:25

Here are the few errors to fix:

in the Selection class:

class Selection : Record
{
    var selectionId: Int64?
    var position: Int?
    var idSong: Int64?

    static let song = hasOne(Song.self, using: ForeignKey(["songId"], to: ["idSong"]))
    var song: QueryInterfaceRequest<Song> {
        request(for: Selection.song)
    }

    // ...
}

in the Song class, remove the useless code, so we get:

class Song : Record
{
    var songId: Int64?
    var name: String?
    var artist: String?

    // ...
}

and finally, in SelectionSong:

struct SelectionSong : FetchableRecord
{
    let selection: Selection
    let song: Song
    
    
    init(row: Row)
    {
        selection = Selection(row: row)
        song = row["song"]
    }
}

And if you want to use a custom key instead of "song" in SelectionSong, change the following in Selection:

    static let song = hasOne(
        Song.self,
        key: "myCustomKey",
        using: ForeignKey(["songId"], to: ["idSong"]))

How to query a table with a 1-1 association on iOS using GRDB?

1 Answers1