A solution for the N+1 problem when fetching children for parents (Fluent 3)

1_Fetch.swift

import Fluent

func fetchChildren<Parent, ParentID, Child: Model, Result>(
        of parents: [Parent],
        idKey: KeyPath<Parent, ParentID?>,
        via reference: KeyPath<Child, ParentID>,
        on conn: DatabaseConnectable,
        combining: @escaping (Parent, [Child]) -> Result) -> Future<[Result]> where ParentID: Hashable & Encodable {
    let parentIDs = parents.compactMap { $0[keyPath: idKey] }
    let children = Child.query(on: conn)
        .filter(reference ~~ parentIDs)
        .all()

    return children.map { children in
        let lut = [ParentID: [Child]](grouping: children, by: { $0[keyPath: reference] })
        return parents.map { parent in
            let children: [Child]

            if let id = parent[keyPath: idKey] {
                children = lut[id] ?? []
            } else {
                children = []
            }

            return combining(parent, children)
        }
    }
}

2_Example_Entities.swift

import FluentMySQL
import Vapor

struct Document: MySQLModel, Migration, Content {
    var id: Int?
    let title: String
}

struct DocImage: MySQLModel, Migration, Content {
    var id: Int?
    let url: String
    var documentID: Document.ID

    init(id: Int? = nil, url: String, documentID: Document.ID) {
        self.id = id
        self.url = url
        self.documentID = documentID
    }

    static func prepare(on conn: MySQLDatabase.Connection) -> Future<Void> {
        return MySQLDatabase.create(DocImage.self, on: conn) { builder in
            builder.field(for: \.id, isIdentifier: true)
            builder.field(for: \.url)
            builder.field(for: \.documentID)
            builder.reference(from: \.documentID, to: \Document.id, onUpdate: .cascade, onDelete: .cascade)
        }
    }
}

struct DocAuthor: MySQLModel, Migration, Content {
    var id: Int?
    let name: String
    var documentID: Document.ID

    init(id: Int? = nil, name: String, documentID: Document.ID) {
        self.id = id
        self.name = name
        self.documentID = documentID
    }

    static func prepare(on conn: MySQLDatabase.Connection) -> Future<Void> {
        return MySQLDatabase.create(DocAuthor.self, on: conn) { builder in
            builder.field(for: \.id, isIdentifier: true)
            builder.field(for: \.name)
            builder.field(for: \.documentID)
            builder.reference(from: \.documentID, to: \Document.id, onUpdate: .cascade, onDelete: .cascade)
        }
    }
}

final class SeedDocs: Migration {
    static func revert(on conn: MySQLConnection) -> EventLoopFuture<Void> {
        return conn.future()
    }

    typealias Database = MySQLDatabase

    static func prepare(on conn: MySQLDatabase.Connection) -> Future<Void> {
        let doc1 = Document(id: 1, title: "Caring for your turtle")
        let author1 = DocAuthor(id: 1, name: "PBF", documentID: 1)
        let doc2 = Document(id: 2, title: "Sleeping is overrated")
        let author2_1 = DocAuthor(id: 2, name: "vzsg", documentID: 2)
        let author2_2 = DocAuthor(id: 3, name: "the vapor community", documentID: 2)
        let image2_1 = DocImage(id: 1, url: "this is an image URL", documentID: 2)
        let image2_2 = DocImage(id: 2, url: "this is another image URL", documentID: 2)
        let doc3 = Document(id: 3, title: "No fun")
        let doc4 = Document(id: 4, title: "Creative Commons 0 Picturebook")
        let image4_1 = DocImage(id: 3, url: "this is an image URL", documentID: 4)
        let image4_2 = DocImage(id: 4, url: "this is another image URL", documentID: 4)

        let docsSaved: LazyFuture<Void> = {
            [doc1, doc2, doc3, doc4].map { $0.create(on: conn) }
                .flatten(on: conn)
                .transform(to: ())
        }

        let authorsSaved: LazyFuture<Void> = {
            [author1, author2_1, author2_2].map { $0.create(on: conn) }
                .flatten(on: conn)
                .transform(to: ())
        }

        let imagesSaved: LazyFuture<Void> = {
            [image2_1, image2_2, image4_1, image4_2].map { $0.create(on: conn) }
                .flatten(on: conn)
                .transform(to: ())
        }

        return [docsSaved, authorsSaved, imagesSaved].syncFlatten(on: conn)
    }
}

4_Example_ResponseType.swift

import Vapor

struct ExtendedDocument: Content {
    let document: Document
    let authors: [DocAuthor]
    let images: [DocImage]

    init(_ document: Document, authors: [DocAuthor] = [], images: [DocImage] = []) {
        self.document = document
        self.authors = authors
        self.images = images
    }
}

5_Example_routes.swift

// ...

router.get("alldocs") { req -> Future<[ExtendedDocument]> in
    Document.query(on: req).all()
        .flatMap { docs -> Future<[ExtendedDocument]> in
            fetchChildren(
                of: docs,
                idKey: \Document.id,
                via: \DocAuthor.documentID,
                on: req,
                combining: { doc, authors in ExtendedDocument(doc, authors: authors) })
        }
        .flatMap { docs -> Future<[ExtendedDocument]> in
            fetchChildren(
                of: docs,
                idKey: \ExtendedDocument.document.id,
                via: \DocImage.documentID,
                on: req,
                combining: { doc, images in ExtendedDocument(doc.document, authors: doc.authors, images: images) })
        }
}

// ...

6_Example_configure.swift

import FluentMySQL
import Vapor

/// Called before your application initializes.
public func configure(_ config: inout Config, _ env: inout Environment, _ services: inout Services) throws {
    try services.register(FluentMySQLProvider())

    // ...
    
    var migrations = MigrationConfig()
    migrations.add(model: Document.self, database: .mysql)
    migrations.add(model: DocAuthor.self, database: .mysql)
    migrations.add(model: DocImage.self, database: .mysql)
    migrations.add(migration: SeedDocs.self, database: .mysql)
    services.register(migrations)
}

Is ~~ a custom operator?

It's an operator from Fluent.. Need to import Fluent to get that running.