Grdb.swift: How to query a table with a 1-1 association?

[mregnauld]

What did you do?

I try to query a table with a 1-1 association.

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

----------
- 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, using: ForeignKey(["songId"], to: ["idSong"]))
    var song: QueryInterfaceRequest<Song> {
        request(for: Selection.song)
    }

    // ...
}

• the Song entity:

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

    // ...
}

• 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(required: Selection.song)
    let list = try SelectionSong.fetchAll(db, request)

What did you expect to happen?

I expect to get a list of SelectionSong in the list variable.

What happened instead?

I get the following error:

Fatal error: missing scope 'selections'

I'm sure I'm close to finding the solution, but I really don't manage to figure out what's wrong in my code.

Environment

GRDB flavor(s): GRDB
GRDB version: 5.3.0
Installation method: SPM
Xcode version: 12.4
Swift version: 5.3
Platform(s) running GRDB: iOS
macOS version running Xcode: macOS Big Sur

[groue]

Hello @mregnauld,

You are close indeed 🙂. There are only two problems in your code.

To make them clear, let's print the row by adding the line below, at the beginning of SelectionSong.init(row:):

print(row.debugDescription)

We'll see the row that SelectionSong attempts at decoding into the selection and song properties. For example, you can get:

▿ [selectionId:1 position:1 idSong:1]
  unadapted: [selectionId:1 position:1 idSong:1 songId:1 name:"Love Me Tender" artist:"Elvis"]
  - song: [songId:1 name:"Love Me Tender" artist:"Elvis"]

First we see the row itself: [selectionId:1 position:1 idSong:1]. It's a Selection row. This is to be expected, because we perform a request Selection.including(required: Selection.song) which is a request of Selection (with extra song topping).

So let's decode the Selection property from the row:

-selection = row[Selection.databaseTableName]
+selection = Selection(row: row)

Next we see that the row for Song ([songId:1 name:"Love Me Tender" artist:"Elvis"]) is stored at the key song. Not songs (as is Song.databaseTableName, if I understand well):

-song = row[Song.databaseTableName]
+song = row["song"]

These two changes will fix your code:

struct SelectionSong: FetchableRecord {
    let selection: Selection
    let song: Song

    init(row: Row) {
        selection = Selection(row: row)
        song = row["song"]
    }
}

Those topics are covered with more details in the "Decoding a Joined Request with FetchableRecord" and "Debugging Request Decoding" chapters of the Associations Guide.

[mregnauld]

Finally working!
Merci beaucoup :wink:

That said, it feels a bit uncomfortable that the key is automatically song, I was expecting to use the table name.
Is it possible then to customize that key?

[mregnauld]

OK, I figured that out. I can customize the key by doing the following:

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

[groue]

C'est exactement ça, you got it :-)

[mregnauld]

Thanks again for your quick and detailed answers, that really helps a lot 🙂