Confusion over .including(all:) query

[sobri909]

Hi! Long time! Hope you're doing well 😄

So I'm doing a rewrite of LocoKit, this time using GRDB's model classes and structs more directly, in the hope that I can get away with not using my custom caching tricks this time around.

Anyway, as part of the rebuild process I'm running into a weirdness that I can't seem to debug. I'm attempting to fetch an array of TimelineItemBase rows, with the associates LocomotionSample rows hanging off of each.

public struct TimelineItem: FetchableRecord, Decodable {
    public var base: TimelineItemBase
    public var samples: [LocomotionSample]
}

The test query is:

let request = TimelineItemBase
    .including(all: TimelineItemBase.samples)
do {
    let items = try await Database.pool.read {
        try TimelineItem.fetchAll($0, request)
    }
    print("items: \(items.count)")
} catch {
    print("error: \(error)")
}

Which is producing the error:

column not found: "_id" - row: [id:"80ACB360-E25F-4D66-9472-F92146C84D62" date:"2024-03-19 08:44:51.000" source:"LocoKit" secondsFromGMT:19800 movingState:0 recordingState:1 timelineItemId:"19A6508A-F3D7-49FC-908A-8C17E67FC7D8" latitude:5.969590223399893 longitude:80.44677346952301 altitude:0.0 horizontalAccuracy:3140.0767850671664 verticalAccuracy:-1.0 speed:0.0 course:0.0 classifiedActivityType:NULL confirmedActivityType:NULL stepHz:NULL xyAcceleration:NULL zAcceleration:NULL grdb_timelineItemId:"19A6508A-F3D7-49FC-908A-8C17E67FC7D8"] currentItem.dateRange: 2024-03-19 09:18:09 +0000 to 2024-03-19 09:21:16 +0000

I'm really struggling to figure it out. When I prepare the statement and print it all I see is the base query of "SELECT * FROM TimelineItemBase", so I don't get to see the subquery / join details, and I've so far failed to step through to their generation in the debugger.

In the debugger it looks to me that it throws the exception when preparing the subquery/join for LocomotionSample, but I can't yet make sense of why. The mention of an _id table is mysterious, and also curious that there's both timelineItemId and a generated grdb_timelineItemId duplicating it.

I'm kind of stuck for where to look next! Any minor help would be greatly appreciated! Thanks 😄

---

The table schemas are thus (with [hopefully] irrelevant bits trimmed out):

try db.create(table: "TimelineItemBase") { table in
    table.column("id", .text).primaryKey()
    table.column("isVisit", .boolean).notNull()
    table.column("startDate", .datetime).indexed()
    table.column("endDate", .datetime).indexed()
    table.column("source", .text).notNull()
    table.column("deleted", .boolean).notNull()

    table.column("previousItemId", .text).indexed()
        .references("TimelineItemBase", onDelete: .setNull, deferred: true)
        .check(sql: "previousItemId != id AND (previousItemId IS NULL OR deleted = 0)")

    table.column("nextItemId", .text).indexed()
        .references("TimelineItemBase", onDelete: .setNull, deferred: true)
        .check(sql: "nextItemId != id AND (nextItemId IS NULL OR deleted = 0)")

    table.column("stepCount", .integer)
    table.column("floorsAscended", .integer)
    table.column("floorsDescended", .integer)
    table.column("averageAltitude", .double)
    table.column("activeEnergyBurned", .double)
    table.column("averageHeartRate", .double)
    table.column("maxHeartRate", .double)
}

try db.create(table: "LocomotionSample") { table in
    table.column("id", .text).primaryKey()
    table.column("date", .datetime).notNull().indexed()
    table.column("source", .text).notNull()
    table.column("secondsFromGMT", .integer).notNull()
    table.column("movingState", .integer).notNull()
    table.column("recordingState", .integer).notNull()

    table.column("timelineItemId", .text).indexed()
        .references("TimelineItemBase", onDelete: .setNull, deferred: true)

    // CLLocation
    table.column("latitude", .double)
    table.column("longitude", .double)
    table.column("altitude", .double)
    table.column("horizontalAccuracy", .double)
    table.column("verticalAccuracy", .double)
    table.column("speed", .double)
    table.column("course", .double)

    table.column("classifiedActivityType", .text)
    table.column("confirmedActivityType", .text)

    // motion sensor data
    table.column("stepHz", .double)
    table.column("xyAcceleration", .double)
    table.column("zAcceleration", .double)
}

And models thus:

@Observable
public class TimelineItemBase: Record, Identifiable, Codable {

    public var id: String = UUID().uuidString
    public let isVisit: Bool
    public let startDate: Date
    public let endDate: Date
    public var source: String = "LocoKit"
    public var deleted = false

    // extended
    public var stepCount: Int?
    public var floorsAscended: Int?
    public var floorsDescended: Int?
    public var averageAltitude: CLLocationDistance?
    public var activeEnergyBurned: Double?
    public var averageHeartRate: Double?
    public var maxHeartRate: Double?

    var dateRange: DateInterval {
        return DateInterval(start: startDate, end: endDate)
    }

    public var previousItemId: String? {
        didSet {
            // TODO: move these to SQL constraints?
            if previousItemId == id { fatalError("Can't link to self") }
            if previousItemId != nil, previousItemId == nextItemId {
                fatalError("Can't set previousItem and nextItem to the same item")
            }
        }
    }

    public var nextItemId: String? {
        didSet {
            // TODO: move these to SQL constraints?
            if nextItemId == id { fatalError("Can't link to self") }
            if nextItemId != nil, previousItemId == nextItemId {
                fatalError("Can't set previousItem and nextItem to the same item")
            }
        }
    }

    public static let samples = hasMany(LocomotionSample.self, using: ForeignKey(["timelineItemId"], to: ["id"])).forKey("samples")

    public override class var databaseTableName: String { return "TimelineItemBase" }

    ...
}

@Observable
public class LocomotionSample: Record, Identifiable, Codable {

    public var id: String = UUID().uuidString
    public var date: Date
    public var secondsFromGMT: Int
    public var source: String = "LocoKit"
    public let movingState: MovingState
    public let recordingState: RecordingState

    // foreign key
    public var timelineItemId: String?

    // CLLocation
    public let latitude: CLLocationDegrees?
    public let longitude: CLLocationDegrees?
    public let altitude: CLLocationDistance?
    public let horizontalAccuracy: CLLocationAccuracy?
    public let verticalAccuracy: CLLocationAccuracy?
    public let speed: CLLocationSpeed?
    public let course: CLLocationDirection?

    // strings for now, until classifier stuff is ported over
    public var classifiedActivityType: String?
    public var confirmedActivityType: String?

    // motion sensor data
    public var stepHz: Double?
    public var xyAcceleration: Double?
    public var zAcceleration: Double?

    public override class var databaseTableName: String { return "LocomotionSample" }

    ...
}

[sobri909]

Oh, the ForeignKey(["timelineItemId"], to: ["id"]) on the toMany() was a failed attempt to work around the problem (if I'm even identifying the problem correctly). It achieved nothing, with identical results to the plain public static let samples = hasMany(LocomotionSample.self).forKey("samples") declaration.

[sobri909]

Oh I missed out the row decoding:

@Observable
public class LocomotionSample: Record, Identifiable, Codable {

    ...

    required init(row: Row) throws {
        id = row["id"]
        date = row["date"]
        secondsFromGMT = row["secondsFromGMT"]
        source = row["source"]
        movingState = MovingState(rawValue: row["movingState"])!
        recordingState = RecordingState(rawValue: row["recordingState"])!

        timelineItemId = row["timelineItemId"]

        latitude = row["latitude"]
        longitude = row["longitude"]
        altitude = row["altitude"]
        horizontalAccuracy = row["horizontalAccuracy"]
        verticalAccuracy = row["verticalAccuracy"]
        speed = row["speed"]
        course = row["course"]

        classifiedActivityType = row["classifiedActivityType"]
        confirmedActivityType = row["confirmedActivityType"]

        try super.init(row: row)
    }

    ...
}

@Observable
public class TimelineItemBase: Record, Identifiable, Codable {

    ...

    required init(row: Row) throws {
        id = row["id"]
        source = row["source"]
        isVisit = row["isVisit"]
        startDate = row["startDate"]
        endDate = row["endDate"]
        deleted = row["deleted"]

        previousItemId = row["previousItemId"]
        nextItemId = row["nextItemId"]

        stepCount = row["stepCount"]
        floorsAscended = row["floorsAscended"]
        floorsDescended = row["floorsDescended"]
        averageAltitude = row["averageAltitude"]
        activeEnergyBurned = row["activeEnergyBurned"]
        averageHeartRate = row["averageHeartRate"]
        maxHeartRate = row["maxHeartRate"]

        try super.init(row: row)
    }

    ...
}

I'm currently fiddling about in the debugger, trying to breakpoint in row decoding to see if I can find my whoopsie there. No luck so far. Feels like it's got to be some dumb typo, or silly misunderstanding of API, but I just can't see it yet 😑

[groue]

Hello @sobri909,

Thanks for sponsoring the project! 💝

The mention of an _id table is mysterious

Agreed.

I suspect @Observable.

I find a mention of _id in https://callistaenterprise.se/blogg/teknik/2023/08/14/new-swift-observation/.

This article mentions underscore-prefixed keys as well: https://www.hackingwithswift.com/books/ios-swiftui/adding-codable-conformance-to-an-observable-class.

Maybe the best technique is to remove Codable conformance, and provide an explicit implementation of encode(to:) on the side of init(row:).

That's a lot of boilerplate 😬

Maybe detaching the data layer from SwiftUI could help (i.e. have some UI observable objects feed from immutable record structs). Codable structs don't need explicit encode(to:) and init(row:). And given the bad interaction of @Observable and Codable, insisting on fighting the ~immaturity~ limits of Apple code might be the wrong way.

[sobri909]

Ooh, you got it! Thanks!

Commenting out @Obserable fixes it! 🎉

In retrospect, I should've been clued by the underscore. But I was too busy being convinced it was something to do with me using GRDB wrong. Heh.

For structs vs classes, yeah I initially went that way with the rewrite. But as I ported over more of the complexity from the old codebase it became clear structs weren't going to be up to challenge. I still have to cut corners for efficiency reasons in some places, so copying/refetching structs is still going to be too costly. Can't win 'em all.

Hopefully I'll be able to get away with only using Record classes for the core high-pressure models, and can rely on struct models for the rest. Fingers crossed.

Anyway, thanks again! It would've taken me forever to get to realising it might be to do with @Observable.

[groue]

Great, @sobri909 👍 I agree that structs are not a panacea, especially large ones!

Shall we close this issue? You can always open a new one if you have another question.

[sobri909]

Yep, problem conclusively solved 👍🏼 Thanks again!