Grdb.swift: Best way to access a single column of an associated record?

[micpringle]

I have the following query setup, including a required relationship...

let coverageLevel = Entity.Fixture.competition.select(Column("coverageLevel"))
let fixtures = try Entity.Fixture
  .including(required: coverageLevel)
  .filter((timestamps[0]...timestamps[1]).contains(Column("scheduledStartTimestamp")))
  .filter(Column("status") <= StatusTransformer.maxStatusValue)
  .order([Column("competitionId"), Column("scheduledStartTimestamp"), Column("id")])
  .fetchAll(db)

Normally, whenever I do this I create a compound POSO that has a property for each model type. However, as I'm only extracting a single property from the relationship, in this context, it seems a bit overkill. What I've done instead is define coverageLevel on Entity.Fixture as an optional, and then implement both init(row:) and encode(to:) to populate the property on read, and ignore the property on write.

init(row: Row) {
  // ...
  self.coverageLevel = Entity.Competition.CoverageLevel(rawValue: row.unadapted["coverageLevel"])
  // ...
}

I believe the row is automatically split into scopes, but I'm using unadapted as I can't seem to find a proper way to access the scopes. I thought I'd be able to do something like row["competition"]["coverageLevel"] but it doesn't appear to work.

Is this the correct way of doing things?

Environment

GRDB flavor(s): GRDB
GRDB version: 3.7.0
Installation method: CocoaPods
Xcode version: 10.2.1
Swift version: 4.2
Platform(s) running GRDB: iOS
macOS version running Xcode: 10.14.5

[groue]

Hello @micpringle,

I believe the row is automatically split into scopes, but I'm using unadapted as I can't seem to find a proper way to access the scopes.

OK. We're entering quite advanced vocabulary here, so thank you for taking the time to look closely at the topic before asking. This is greatly appreciated because we'll be able to go straight to the point.

First, fixtures.including(required: competition) indeed "hides" the competition columns into a scope. And I think it's safer, because this makes sure no competition column could pollute the decoding of fixtures. In the example below, A.init(row:) can not access B's name with row["name"]:

--     <root scope> <b scope   >
SELECT a.id, ...,   b.name, ... FROM a JOIN b ...

So the scoping of associations is unlikely to be relaxed: it is a safety belt.

Back to your issue. You indeed have one problem with fixtures.including(required: competition.select("coverageLevel")): you can't access the competition column you're interested into from the root scope (from Fixture.init(row:)), unless you use row.unadapted which exposes the raw fetched row, without scopes.

Be assured you are not the first to meet this issue - I have, too, several times. And a slightly related feature that I met a couple of days ago was to fetch the mere presence of an associated record into a boolean accessible right from the root scope.

So, congrats for finding a way to make it work 👍

---

Now, on to some alternative takes.

We'll use below:

• TableAliases, that allow you to refer to any table inside an associated request.
• the annotated(with:) method which extends the selection without creating any nested scope.

With this technique, you still have an extra coverageLevel property in the Fixture type, but you no longer have to use the unadapted row in Fixture.init(row:). The coverageLevel column is present into the root fixture scope:

// SELECT fixture.*, competition.coverageLevel FROM ...
let competitionAlias = TableAlias()
let fixtures = try Entity.Fixture
    // Annotate the Fixture with the coverageLevel column from competition
    .annotated(with: competitionAlias[Column("coverageLevel")])
    // Don't include any column from competition with `joining(required:)`
    .joining(required: Entity.Fixture.competition.aliased(competitionAlias))
    .fetchAll(db)

// Fixture.init(row:)
init(row: Row) {
    ...
    self.coverageLevel = row["coverageLevel"] // nil if column is missing
}

Side note: I discover that I forgot to document the fact that annotated(with:) accepts not only association aggregates, but generally anything can be selected. I'll have to fix this.

---

A second take is to leave the Fixture record pristine, without any coverageLevel property, and to declare a new type since we're decoding something which is not a fixture:

struct FixtureInfo: FetchableRecord {
    let fixture: Fixture
    let coverageLevel: Int
    init(row: Row) {
        let fixture = Fixture(row: row)
        let coverageLevel = row["coverageLevel"]
    }
}

let competitionAlias = TableAlias()
let fixtures = try Entity.Fixture
    .annotated(with: competitionAlias[Column("coverageLevel")])
    .joining(required: Entity.Fixture.competition.aliased(competitionAlias))
    // Change the type of fetched records
    .asRequest(of: FixtureInfo.self)
    .fetchAll(db)

There are several benefits:

• the coverageLevel property can be a non-Optional if we know that the required competition has one
• the Fixture type is not "infected" by the part of your application that needs to run this request.

I personally tend to use a lot of those extra types FooInfo or CompleteBar or BazItem in my apps (yeah, naming them is sometimes tricky). They provide all the isolation and type safety I need to feel comfortable.

I will not tell which one of the approaches above is the best. You are at the best place to make up your mind. And we have discussed useful techniques that you may be happy to reuse one day :-)

[groue]

I think your question deserves the "best practices" label, because it is very precise and focused, and it was possible to provide a great deal of information in the answer. The title is lousy, though, we should find a better one :-)

[micpringle]

Hey @groue–thanks for the prompt response and the kind words. 😄

In this instance I'm inclined to go with the table alias approach, but I'm wondering if this is specific to a version of GRDB later than 3.7.0 as...

let alias = TableAlias()
let fixtures = try Entity.Fixture
  .annotated(with: alias[Column("coverageLevel")])
  .joining(required: Entity.Fixture.competition.aliased(alias))

generates the following compiler error...

Cannot subscript a value of type 'TableAlias' with an index of type 'Column'

If I move .joining above .annotated then I get this one instead...

Cannot invoke 'annotated' with an argument list of type '(with: SQLExpression)'

Any ideas?

[EDIT]

Looks like it may have been added here, so definitely in a version later than what I'm using.

[groue]

Yes: the generalized annotated(with:) method was added in GRDB 4!

What about checking Migrating From GRDB 3 to GRDB 4?

[micpringle]

I've updated to v4 and I'm now up and running using the table alias solution–thanks! 🙏

However, I have run into another small _issue_. As I'm implementing init(row:) I lose all the functionality GRDB provides for free in its default implementation, such as decoding enum types.

I have two enums I need to decode in init(row:) and have ended up with the following...

var coverageLevel = Entity.Competition.CoverageLevel.resultOnly
var status = Status.preMatch

if let dbValue = row["coverageLevel"] as? Int64, let rawValue = Int(exactly: dbValue), let decodedValue = Entity.Competition.CoverageLevel(rawValue: rawValue) {
  coverageLevel = decodedValue
}

if let dbValue = row["status"] as? Int64, let rawValue = Int(exactly: dbValue), let decodedValue = Status(rawValue: rawValue) {
  status = decodedValue
}

Is there a way I can use (_some part of_) GRDB to provide this functionality for me, since it does it automatically if I don't implement init(row:)?

[micpringle]

@groue Please ignore my previous comment–it turns out I've been a bit overzealous and didn't need to implement init(row:). Just implementing encode(to:) so GRDB knows to ignore the injected coverageLevel property when writing is enough.

Thanks again for all your help–I'll mark this as closed know.

[groue]

OK @micpringle

Yet, in the future, if you have to decode enums in a customized init(row:), add DatabaseValueConvertible conformance to your enum (works like a charm as long as the enum is backed by Int, Sring, etc):

enum MyEnum: Int { ... }

extension MyEnum: DatabaseValueConvertible { } // <-

struct MyRecord: FetchableRecord {
    var myEnum: MyEnum
    init(row: Row) {
        myEnum = row["myEnum"] // Sure
    }
}

See Swift Enums for more information.