convention for fetching lists of hasMany records with their parent(s)

[mackuba]

I'm wondering what is the best / most convenient way to fetch records from a hasMany relationship together with their parent records (the ones they "belong to"), either one or multiple.

Example: Gallery record hasMany Photos. I need to either:

• fetch a selected gallery and all its photos
• or fetch some number of galleries matching some condition and all their photos

I can do this easily with separate queries, but it seems wasteful (not sure how this actually works with a local database, I come from a Rails background where using more queries than necessary was a very bad thing). Especially in the second case, where it would mean N+1 queries (one photos fetch for each gallery).

I could build some kind of "photo + gallery" helper object and then fetch all matching photos to it, but then I'd get X separate instances of the same gallery in them when I only really need one, and I'd need to manually go through them and find all records whose gallery sub-objects are identical and group them somehow. But this sounds like something that should happen automatically...

What is the best way to do this, so that I can then do something like that:

for record in getAllGalleriesWithPhotos() {
  print("Gallery \(record.gallery.id):")

  for photo in record.photos {
    print("- Photo \(photo.id)")
  }
}

This must be a fairly common use case, so I feel like I'm missing something obvious...

[groue]

Hello @mackuba,

This must be a fairly common use case, so I feel like I'm missing something obvious...

You did not miss anything. It just happens that eager loading of HasMany associations is not implemented yet!

Fortunately, it will ship with GRDB 4 (developped in #479). This feature is currently in development in #505. I'm actually scratching my head on it right now 😅

When it is shipped, you will write:

// 1. Setup
struct Gallery: TableRecord, FetchableRecord, Decodable {
    static let photos = hasMany(Photo.self)
    var id: Int64
    var name: String
}

struct Photo: TableRecord, FetchableRecord, Decodable {
    var id: Int64
    var galleryId: Int64
    var title: String
}

// 2. Fetch all galleries along with their photos
struct GalleryInfo: FetchableRecord, Decodable {
    var gallery: Gallery
    var photos: [Photo]
}

let request = Gallery.including(all: Gallery.photos)
let infos: [GalleryInfo] = try GalleryInfo.fetchAll(db, request)

for info in infos {
    print("\(info.gallery.name) contains:")
    for photo in info.photos {
        print("- \(photo.title)")
    }
}

// With some filtering and ordering
let request = Gallery
    .including(all: Gallery.photos
        .filter(...) // Filter and order photos
        .order(...))
    .filter(...) // Filter and order galleries
    .order(...)

Until then, you have to perform the fetch manually. You have some sample code in #406.

[mackuba]

Awesome, that's exactly what I need, and I'll use the version from that sample code for now, thanks! And thanks for answering so quickly and on a Saturday, you're doing amazing work with all the documentation, support and API design for this project 👍👍

[groue]

Thank you Kuba for your patience :-)