Fetching a hierarchy of records

[matiaskorhonen]

What's the best way to fetch a full hierarchy of records?

Let say that I have three models: Library, Collection, and Book. Libraries have many Collections, which have many Books.

Something like this:

import GRDB

struct Library: FetchableRecord {
    let id: UUID
    let city: String
}

extension Library {
    static let collections = hasMany(Collection.self)
    var collections: QueryInterfaceRequest<Collection> {
        request(for: Library.collections)
    }
}

struct Collection: FetchableRecord {
    let id: UUID
    let name: String
}

extension Collection {
    static let books = hasMany(Book.self)
    var collections: QueryInterfaceRequest<Book> {
        request(for: Collection.books)
    }
}

struct Book: FetchableRecord {
    let id: UUID
    let title: String
}

I'd like to be able to retrieve a hierarchy of all libraries, their collections, and each collection's books in one query.

I imagine it could look something like this, but I'm having issues figuring out the correct request structure for it:

struct LibraryInfo: Decodable, FetchableRecord {
    struct CollectionInfo: Decodable {
        var collection: Collection
        var books: [Book]
    }
    let library: Library
    let collections: [CollectionInfo]
}

[groue]

Hello @matiaskorhonen,

I imagine it could look something like this, but I'm having issues figuring out the correct request structure for it:

Yes I think that's it. This plain Swift structure does encode the hierarchy you're after, right?

You might be bothered by the collections property name, because it's not really an array of Collection. You can rename it:

struct LibraryInfo: Decodable, FetchableRecord {
    struct CollectionInfo: Decodable {
        var collection: Collection
        var books: [Book]
    }
    var library: Library
    var collectionInfos: [CollectionInfo] // customized name
}
let results: [LibraryInfo] = try Library
    .including(all: Library.collections
        .forKey("collectionInfos") // <- don't use the default "collections" name
        .including(all: Collection.books))
    .asRequest(of: LibraryInfo.self)
    .fetchAll(db)

[matiaskorhonen]

Awesome, thanks!