Grdb.swift: Unexpected partial deserialisation...

[micpringle]

What did you do?

Create three structs, added conformance for Decodable, TableRecord, and FetchableRecord, and set up a hasMany relationship between two of them...

struct Tournament {
  enum CoverageLevel: Int, Decodable {
    case full
    case limited
    case resultOnly
  }

  let id: Int
  let name: String
  let category: String
  let order: Int
  let coverageLevel: CoverageLevel
  let seasonId: Int
  let hasRankings: Bool
}

struct TournamentInterval {
  let id: Int
  let tournamentId: Int
  let fromTimestamp: Int
  let untilTimestamp: Int
  let fixtureCount: Int
}

struct TournamentInfo {
  let tournament: Tournament
  let intervals: [TournamentInterval]?
}

extension Tournament: Decodable {}
extension TournamentInterval: Decodable {}
extension TournamentInfo: Decodable {}

extension Tournament: TableRecord, FetchableRecord {
  static let intervals = hasMany(TournamentInterval.self)
}

extension TournamentInterval: TableRecord, FetchableRecord {}
extension TournamentInfo: FetchableRecord {}

I then created the following request...

let request = Tournament
  .including(optional: Tournament.intervals
    .filter(sql: "fromTimestamp == ? AND untilTimestamp == ?", arguments: [timestamps[0], timestamps[1]]))

This generates the following SQL...

SELECT "tournament".*, "tournamentInterval".* FROM "tournament" LEFT JOIN "tournamentInterval" ON (("tournamentInterval"."tournamentId" = "tournament"."id") AND (fromTimestamp == 1556496000 AND untilTimestamp == 1556582399))

And the following if I debug using Row.fetchAll(db, request)...

[▿ [id:1848 name:"Africa Cup of Nations Qualifiers" category:"International" order:3 coverageLevel:1 seasonId:40265 hasRankings:0]
  unadapted: [id:1848 name:"Africa Cup of Nations Qualifiers" category:"International" order:3 coverageLevel:1 seasonId:40265 hasRankings:0 id:1 tournamentId:1848 fromTimestamp:1556496000 untilTimestamp:1556582399 fixtureCount:12]
  - intervals: [id:1 tournamentId:1848 fromTimestamp:1556496000 untilTimestamp:1556582399 fixtureCount:12]]

What did you expect to happen?

When I use TournamentInfo.fetchAll(db, request) I expect the object to be decoded.

What happened instead?

Only the tournament property gets decoded–intervals is simply set to nil are per the following output...

[Sp_Score.TournamentInfo(tournament: Sp_Score.Tournament(id: 1848, name: "Africa Cup of Nations Qualifiers", category: "International", order: 3, coverageLevel: Sp_Score.Tournament.CoverageLevel.limited, seasonId: 40265, hasRankings: false), intervals: nil)]

Are you able to advise on where I'm going wrong? These has been driving me nuts for the past couple of hours.

Environment

GRDB flavor(s): GRDB
GRDB version: 3.7.0
Installation method: (CocoaPods, SPM, manual?) Cocoapods
Xcode version: 10.2.1
Swift version: 4.2
Platform(s) running GRDB: (iOS, macOS, watchOS?) iOS
macOS version running Xcode: 10.14.4

[groue]

Hello @micpringle,

GRDB can not yet decode arrays of associated records:

struct TournamentInfo {
  let tournament: Tournament
  let intervals: [TournamentInterval] // <- not supported yet
}

This feature is called "eager loading of has-many associations", and it will come eventually.

Meanwhile, please check #406, which provides a workaround.

[micpringle]

Thanks for the prompt response @groue–I think the term hasMany and the natural relationship between Book and Author (an author will generally writes more than one book) in all the examples may have confused me somewhat. 😁

In the example above only one TournamentInterval can exist for each time period, which is why I'm filtering the association–so could my approach be changed to better reflect this? (i.e. I don't actually need an array here)

Essentially what I'm attempting to do is get the fixtureCount for the given period from tournamentInterval for each tournament in tournament, if that helps.

In the meantime I'll be sure to read through #406.

[groue]

In the example above only one TournamentInterval can exist for each time period, which is why I'm filtering the association–so could my approach be changed to better reflect this? (i.e. I don't actually need an array here)

If you are sure the hasMany associations can only match one or zero TournamentInterval, then the hasMany association behaves like a hasOne association, and you can write:

struct TournamentInfo {
    let tournament: Tournament
    let interval: TournamentInterval?
}

let intervals = Tournament.intervals
    .filter(sql: "fromTimestamp == ? AND untilTimestamp == ?", arguments: [timestamps[0], timestamps[1]])
    .forKey("interval") // Match the TournamentInfo.interval property name
let request = Tournament.including(optional: intervals)

(If it happens that more than one TournamentInterval match, you'll get duplicate tournaments).

I guess that what you missed was this forKey method, which makes the glue between the request and the decoded struct.

Essentially what I'm attempting to do is get the fixtureCount for the given period from tournamentInterval for each tournament in tournament, if that helps.

You may want to have a look at the .select method (Tournament.intervals.select(Column("fixtureCount"))...), but I'm not sure at all this could help significantly improve the code.

[micpringle]

Hey @groue,

After a bit investigation I found the best way to resolve this particular instance was to drop the model classes TournamentInfo and TournamentInterval all together (_although the latter still exists in the schema_), and update Tournament to include fixtureCount...

struct Tournament {
  enum CoverageLevel: Int, Decodable {
    case full
    case limited
    case resultOnly
  }

  let id: Int
  let name: String
  let category: String
  let order: Int
  let coverageLevel: CoverageLevel
  let seasonId: Int
  let hasRankings: Bool
  let fixtureCount: Int // <- added
}

extension Tournament: Decodable {}

extension Tournament: TableRecord, FetchableRecord {}

Then execute the necessary SQL directly on Tournament so that the results deserialize into instances of that model class...

let sql = """
  SELECT "tournament".*, "tournamentInterval".fixtureCount \
  FROM "tournament" \
  LEFT JOIN "tournamentInterval" ON (("tournamentInterval"."tournamentId" = "tournament"."id") AND (fromTimestamp = ? AND untilTimestamp = ?));
"""

let tournaments = try Tournament.fetchAll(db, sql, arguments: [timestamps[0], timestamps[1]])

This works perfectly, and only requires a single trip to the db. 😄

[groue]

Perfect, @micpringle! 👍

For the record, GRDB can generate the same SQL:

// SELECT "tournament".*, "tournamentInterval"."fixtureCount"
// FROM "tournament"
// LEFT JOIN "tournamentInterval" ON
//  (("tournamentInterval"."tournamentId" = "tournament"."id")
//  AND (("tournamentInterval"."fromTimestamp" = ?)
//  AND ("tournamentInterval"."untilTimestamp" = ?)))
let intervals = TableAlias()
let request = Tournament
    .joining(optional: Tournament.intervals
        .filter(Column("fromTimestamp") == timestamps[0])
        .filter(Column("untilTimestamp") == timestamps[1])
        .aliased(intervals))
    .annotated(with: [intervals[Column("fixtureCount")]])

let tournaments = try request.fetchAll(db)

Thanks for feeding the repo issues with interesting use cases!

[micpringle]

Thanks @groue–I'll be sure to take a look at your suggestion as I'd much prefer to allow GRDB build the SQL than have to hand-code it.

[groue]

The feature has just landed in GRDB 4. Check the updated documentation of Associations.