Prefetching a 'required hasMany relationship' can have unexpected results.

[dawilliams-gpsw]

Hello all! I was recently using the .including(required:) API and noticed some behavior that I didn't catch when first reading through the documentation on associations. In particular, this part in this section — https://github.com/groue/GRDB.swift/blob/master/Documentation/AssociationsBasics.md#includingrequired , emphasis mine:

This method accepts any association. It has the base record fetched along with one associated record, which is "included" in the fetched results. When the associated record does not exist, records are not present in the fetched results: the associated record is "required".

Should this API be restricted to types that are not to-many relationships, or would that break compositional query language described in the docs? It was confusing for me, because I was expecting to prefetch all of the relationships, similar to including(all:) to fill out a 'info' type struct:

struct FooWithBars: Decodable {
  var foo: Foo
  var bars: [Bar]?
}

// ...

try Foo.including(required: foo.bars).asRequest(of: FooWithBars.self).fetchAll(db)

I see that this uses a row adapter under the hood and only joins the first record in the relationship, and thus my bars are nil when I expected them to be the prefetched records. This becomes slightly more obvious when I remove optionality from FooWithBars because the decoding ends up failing and that led me to the docs to find the part I had previously missed regarding only fetching one associated record from the relationship.

If I'm not mistaken, I think the solution for me is to add a .having(...) that checks if the association is not empty, but I am wondering if this is something that GRDB could (should?) do under the hood if decoding to a collection.

[groue]

Hello @dawilliams-gpsw,

Thanks for asking this question about an obscure feature! The documentation is slightly misleading, so we may have to find a better way to put it.

This method accepts any association.

This is correct. including(required:) accepts to-one and to-many associations.

It has the base record fetched along with one associated record, which is "included" in the fetched results.

The wording should be enhanced.

including(required:) always performs an SQL JOIN. All pairs (base record, associated record) are present in the fetched results. If, say, team A has two players, team B has one player, and team C has no player, we'll get:

struct Team: TableRecord {
    static let players = hasMany(Player.self)
}

struct Membership: FetchableRecord {
    var team: Team
    var player: Player
}

// - (team A, player 1)
// - (team A, player 2)
// - (team B, player 3)
let memberships = try Team
    .including(required: Team.players)
    .asRequest(of: Membership.self)
    .fetchAll(db)

With including(optional:), which performs a LEFT JOIN, team C would also be present in the results, with a nil player.

This use case is not frequent, and I never took the time to document it properly.

I see that this uses a row adapter under the hood and only joins the first record in the relationship.

This is not true, and probably inferred from the wording of the documentation. As said in the previous example, it joins all associated records.

If I'm not mistaken, I think the solution for me is to add a .having(...) that checks if the association is not empty, but I am wondering if this is something that GRDB could (should?) do under the hood if decoding to a collection.

If you want to decode a struct that contains an array of associated records, then you must use including(all:). The including method you choose, and the shape of the decoded record, are tightly related (see Choosing a Joining Method Given the Shape of the Decoded Type for more information).

If you want to avoid empty arrays of associated records, then, yes, .having(association.isEmpty == false) should make it.

[dawilliams-gpsw]

I see! I think maybe my wording was confusing here:

I see that this uses a row adapter under the hood and only joins the first record in the relationship.

This is not true, and probably inferred from the wording of the documentation. As said in the previous example, it joins all associated records.

What I meant was that I end up with 1 row with all of the columns from Team and all of the columns from Player when choosing including(required:) but the .including(all:) gives me 1 row with all of the columns from Team and then carries with it an associated requrest that does another fetch per row to grab all of the Player that are associated. In my head, I imagined this as returning one row with all of the columns from Team and all of the columns from Player (multiplied by the number of associated records) and then split up using some dynamic number of scope adapters based on the count of the associated records. After digging through, I saw that was not the case and that there is a 'supplemental fetch' with each row when using including(all:)

Your response helped me better understand, and I see that it's a matter of getting multiple rows that are joined (like a tuple!) versus multiple rows that carry an associated fetch request (like a task group!).

[groue]

What I meant was that I end up with 1 row with all of the columns from Team and all of the columns from Player when choosing including(required:) but the .including(all:) gives me 1 row with all of the columns from Team and then carries with it an associated requrest that does another fetch per row to grab all of the Player that are associated.

Yes, including(all:) totally performs two requests :-) The first request fetches an array of teams, and the second request fetches an array of all players of all those teams. This array of players is later split by team.

The sample code below shows how to fetch "by hand", if associations would not exist:

struct TeamWithPlayers: FetchableRecord {
    var team: Team
    var players: [Player]
}

// Convenience api based on associations
let teamsWithPlayers = try Team
    .including(all: Team.players)
    .asRequest(of: TeamWithPlayers.self)
    .fetchAll(db)

// The same array, but without associations this time
// (that's what associations perform under the hood)
let teams = try Team.fetchAll(db) // First request
let teamIds = teams.map { $0.id }
let players = try Player          // Second request
    .filter(teamIds.contains(Column("teamId")))
    .fetchAll(db)
let playersByTeamId = Dictionary(grouping: players, by: { $0.teamId })
let teamsWithPlayers = teams.map { team in
    TeamWithPlayers(team: team, players: playersByTeamId[team.id] ?? [])
}

I'm happy that you have a better understanding now. Don't hesitate asking further questions if needed!

[gemcoder21]

@groue hey I have similar question for you, it might be even more complex as it contains more associations.

public struct TransactionAssetAssociationRecord: Codable, TableRecord, FetchableRecord, PersistableRecord  {
    public static var databaseTableName: String = TransactionRecord.databaseTableName + "_assets"
    public var transactionId: String
    public var assetId: String
}

public struct TransactionRecord: Codable, TableRecord, FetchableRecord, PersistableRecord  {
      ....
      static let assets = hasMany(TransactionAssetAssociationRecord.self)
}

var request = TransactionRecord
            .including(all: TransactionRecord.assets)
            .joining(required: TransactionRecord.assets.filter(Columns.TransactionAssetAssociation.assetId == assetId.identifier)
            try request.asRequest(of: TransactionInfo.self)

public struct TransactionInfo: Codable, FetchableRecord {
    public let assets: [TransactionAssetAssociationRecord]
}

This is a snipper of my code, had to reduct some of it as it has many properties. I'm not able to fetch assets: [TransactionAssetAssociationRecord], but ideally it should be a AssetRecord and not just a TransactionAssetAssociationRecord model, can you provide guidance on how to make it better?

[gemcoder21]

@groue I think I was able to figure out how to do it via https://github.com/groue/GRDB.swift/blob/master/Documentation/AssociationsBasics.md#hasmanythrough, thank you for really good documentation!