Creating a model/struct for SQL views

[robbevp]

Hi there 👋

I'm trying to use an SQL view as a read-only table, so that I can read from it and associate it with other tables. This partially works, but I can't seem to figure out which protocols my model needs, or how I need to set this up.

A simplified version of what I have:

try db.create(table: "player") { table in
    table.autoIncrementedPrimaryKey("id")
    table.column("name", .text).notNull()
}

try db.create(table: "goal") { table in
    table.autoIncrementedPrimaryKey("id")
    table.belongsTo("player")
}

try db.create(view: "playerScore", asLiteral: """
   SELECT
     playerId as id, --- Added since GRDB seems to require an `id`-column with unique values for `TableRecord`
     playerId,
     COUNT(*) as count
  FROM goals GROUP BY playerId
""")

struct Player: Identifiable, Equatable, Codable, FetchableRecord, MutablePersistableRecord {
    var id: Int64?
    var name: String
    
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
    
    static let score = hasOne(Score.self)
    var score: QueryInterfaceRequest<Score> { request(for: Player.score) }
}

struct Score: Identifiable, Equatable, Decodable, FetchableRecord, TableRecord {
    var id: Int64
    var playerId: Int64
    var count: Int
    
    static let player = belongsTo(Player.self)
}

Using this I can fetch and filter data from the table without issues:

let scores = try! await self.database.reader.read { db in
    try! Score.filter(Column("count") > 3).order(Column("count")).fetchAll(db)
 }

However, I can't use this record as an association:

struct PlayerInfo: Decodable, FetchableRecord {
  var player: Player
  var score: Score
}

let res = try! await self.database.reader.read { db in
  try! Player.including(optional: Player.score).asRequest(of: PlayerInfo.self).fetchAll(db)
}
// => Fatal error: 'try!' expression unexpectedly raised an error: SQLite error 1: no such table: score

I imagine that I don't need/want to apply `TableRecord` to my `Score` model, since it isn't a table. But I need that to have the association. Is what I want to do possible with GRDB?

[groue]

Hello @robbevp,

Let's start from the error message:

SQLite error 1: no such table: score

The reason why you see this message is that Player.including(optional: Player.score) uses Player.score, an association from Player to Score. GRDB thus looks for the table for Score, which, by default, is named like the record type itself: score.

But your schema has no score table. Instead it has a playerScore view. The fact that it is a view is not a problem (as long as we don't try to write into it). But we need to help GRDB making the link between the table and the view. See the "👉" comments below:

struct Player: Identifiable, Equatable, Codable, FetchableRecord, MutablePersistableRecord {
    var id: Int64?
    var name: String
    
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
    
    // 👉 There is no such thing as a "foreign key" between
    // an SQLite table and an SQLite view. So we have to fully
    // instruct GRDB of the relationship. The `hasOne` association
    // wants a foreign key from the referrer to the referred:
    // playerScore.id to player.id.
    static let scoreForeignKey = ForeignKey(["id"], to: ["id"])
    static let score = hasOne(Score.self, using: scoreForeignKey)
    var score: QueryInterfaceRequest<Score> { request(for: Player.score) }
}

struct Score: Identifiable, Equatable, Decodable, FetchableRecord, TableRecord {
    var id: Int64
    var playerId: Int64
    var count: Int
    
    // 👉 Replace the default table name "score"
    static let databaseTableName = "playerScore"
}

And now it should work:

struct PlayerInfo: Decodable, FetchableRecord {
    var player: Player
    var score: Score? // 👉 optional because some players have no registered goal
}

// SELECT player.*, playerScore.* 
// FROM player
// JOIN playerScore ON playerScore.id = player.id
try Player
    .including(optional: Player.score)
    .asRequest(of: PlayerInfo.self)
    .fetchAll(db)

Finally, regarding your comment in the view definition:

playerId as id, --- Added since GRDB seems to require an id-column with unique values for TableRecord

GRDB does not require anything. But your app defines Score as Identifiable. Thus Score needs an id property. Having the view define a column with the same name surely helps supporting this Identifiable conformance.

If you prefer playerId, just go for it, as in the sample code below.

• Remove the id column in the playerScore definition.
• Change the foreign key so that it links playerScore.playerId to player.id.
• Address the eventual Identifiable conformance in Swift, instead of SQL.

// Variant
let dbQueue = try DatabaseQueue()

try dbQueue.write { db in
    try db.create(table: "player") { table in
        table.autoIncrementedPrimaryKey("id")
        table.column("name", .text).notNull()
    }
    
    try db.create(table: "goal") { table in
        table.autoIncrementedPrimaryKey("id")
        table.belongsTo("player")
    }
    
    try db.create(
        view: "playerScore",
        asLiteral: "SELECT playerId, COUNT(*) AS count FROM goal GROUP BY playerId")
}

struct Player: Identifiable, Equatable, Codable, FetchableRecord, MutablePersistableRecord {
    var id: Int64?
    var name: String
    
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
    
    // 👉 There is no such thing as a "foreign key" between
    // an SQLite table and an SQLite view. So we have to fully
    // instruct GRDB of the relationship. The `hasOne` association
    // wants a foreign key from the referrer to the referred:
    // playerScore.playerId to player.id.
    static let scoreForeignKey = ForeignKey(["playerId"], to: ["id"])
    static let score = hasOne(Score.self, using: scoreForeignKey)
    var score: QueryInterfaceRequest<Score> { request(for: Player.score) }
}

struct Score: Equatable, Decodable, FetchableRecord, TableRecord {
    var playerId: Int64
    var count: Int
    
    // 👉 Replace the default table name "score"
    static let databaseTableName = "playerScore"
}

// 👉 If Identifiable conformance is needed, add it in Swift.
// It's better to address Swift concerns... in Swift ;-)
// This is not a database schema concern.
// extension Score: Identifiable {
//     var id: Int64 { playerId }
// }

struct PlayerInfo: Decodable, FetchableRecord {
    var player: Player
    var score: Score? // 👉 optional because some players have no registered goal
}

let res = try dbQueue.read { db in
    // SELECT player.*, playerScore.*
    // FROM player
    // LEFT JOIN playerScore ON playerScore.playerId = player.id
    try Player
        .including(optional: Player.score)
        .asRequest(of: PlayerInfo.self)
        .fetchAll(db)
}

[groue]

Finally, you may prefer an score: Int property instead of a score: Score? value which is just an indirection.

This is what this third variant does:

struct PlayerInfo: Decodable, FetchableRecord {
    var player: Player
    var score: Int // 0 when no goal has been registered
}

let res = try dbQueue.read { db in
    // SELECT player.*, IFNULL(playerScore.count, 0) AS score
    // FROM player
    // LEFT JOIN playerScore ON playerScore.playerId = player.id
    let score = Player.score.select((Column("count") ?? 0).forKey("score"))
    return try Player
        .annotated(withOptional: score) // Optional because some players have no registered goal
        .asRequest(of: PlayerInfo.self)
        .fetchAll(db)
}

If you only want players with a non-zero score:

struct PlayerInfo: Decodable, FetchableRecord {
    var player: Player
    var score: Int // Never 0
}

let res = try dbQueue.read { db in
    // SELECT player.*, playerScore.count AS score
    // FROM player
    // JOIN playerScore ON playerScore.playerId = player.id
    let score = Player.score.select(Column("count").forKey("score"))
    return try Player
        .annotated(withRequired: score) // Require players that have registered goal(s)
        .asRequest(of: PlayerInfo.self)
        .fetchAll(db)
}

[robbevp]

Thanks for such a quick and thorough answer! I was indeed missing configuration of the foreign key. Adding that was the trick to make this work.

The reason why you see this message is that Player.including(optional: Player.score) uses Player.score, an association from Player to Score. GRDB thus looks for the table for Score, which, by default, is named like the record type itself: score.
But your schema has no score table. Instead it has a playerScore view.

Sorry about this, I simplified and renamed my models for this example to keep the code snippets short, but misnamed the table 🤦

[groue]

I presumed that ;-) Yes, foreign key must be explicit for views. Now, "no such table" is not the real error when what is needed is an explicit ForeignKey. I'll try to provide a better feedback. Happy GRDB, @robbevp!

[groue]

I have improved the diagnostic in 1239178. Now whenever an association involves a view and lacks an explicit ForeignKey, a fatal error will be raised instead of the unhelpful "no such table" error:

Could not infer foreign key from 'origin' to 'destination'. To fix this error, provide an explicit ForeignKey in the association definition.

[robbevp]

Thanks - using GRDB has been a joy so far!