Multiple table results in a single request

[Pomanks]

Hi,

First of all, thank you for providing such an amazing library!
I've been using it for a very limited time but I'm really impressed by how powerful it can be.

I searched around the documentation but have not yet found a way to "append" another table's results into a decodable request…?
By that, I mean another table that has no direct association to the first one, like the following:

// Records
struct Space: Decodable, Hashable, FetchableRecord, Sendable { … }

struct ScientificClassification: Decodable, Hashable, FetchableRecord, Sendable {
    static let plant = belongsTo(Plant.self)
    var plant: QueryInterfaceRequest<Plant> {
        request(for: Self.plant)
    }
}

struct Plant: Decodable, Hashable, FetchableRecord, Sendable {
    static let scientificClassification = hasOne(ScientificClassification.self)
    var scientificClassification: QueryInterfaceRequest<ScientificClassification> {
        request(for: Self.scientificClassification)
    }
}

// FetchKeyRequest
struct Value: Decodable, Hashable, FetchableRecord, Sendable {
    var space: Space
    var plantsInfo: [PlantInfo]

    init(space: Space, plantsInfo: [PlantInfo] = []) {
        self.space = space
        self.plantsInfo = plantsInfo
    }
}


struct PlantInfo: Decodable, Hashable, FetchableRecord, Sendable {
    var plant: Plant
    var scientificClassification: ScientificClassification
}

let scientificClassificationAlias = TableAlias()
let scientificClassification = Plant.scientificClassification
    .aliased(scientificClassificationAlias)
    .forKey("scientificClassification")

let plantsAlias = TableAlias()
let plantsInfoRequest = Plant
    .all()
    .aliased(plantsAlias)
    .including(required: scientificClassification)

let request = Space
    .filter(id: space.id)
// What I'm trying to do:
    .append(plants.forKey("plantsInfo"))


// Later…
try Value.fetchOne(db, request) ?? defaultValue

[groue]

Hello @Pomanks,

It looks like you want to perform two fetches, one for the space, and another one for the plants, and compose the results in your Value type:

func fetch(_ db: Database, spaceId: Space.ID) throws -> Value? {
    // One fetch...
    guard let space = try Space.fetchOne(db, id: spaceId) else {
        return nil
    }

    // Another fetch...
    let plantInfos = try Plant
        .including(required: Plant.scientificClassification)
        .asRequest(of: PlantInfo.self)
        .fetchAll(db)

    // ...et voilà!
    return Value(space: space, plantInfos: plantInfos)
}

[Pomanks]

Hi @groue, it's indeed what I ended up doing. Thank you for confirming!

[groue]

Great! Writing code (i.e. programming) is cool indeed: you can add as many local variables, if, switch, for, etc. as needed, until the program does exactly what you need 😉 Happy GRDB!