Helper type and @Selection reusability

[Pomanks]

Hi!, I've gathered info from both SharingGRDB and StructuredQueries's documentation in order to achieve my goal but I can't get to the final solution yet.

I have a set of tables, defined as follows:

@Table
struct Space {
   let id: ID
}

@Table
struct Item {
   let id: ID
   let plantID: Plant.ID
   let spaceID: Space.ID?
}

@Table
struct Plant {
   let id: ID
   let categoryID: Category.ID?
}

@Table
struct Category {
   let id: ID
}

@Table
struct ScientificClassification {
   let id: ID
   let plantID: Plant.ID
   let familyID: Family.ID?
}

@Table
struct Family {
   let id: ID
}

Later on, I need to wrap those tables into a single Selection for easier use, like below:

@Selection
struct SingleReference {
   let item: Item
   let plant: Plant
   let category: Category?
   let scientificClassification: ScientificClassification
   let family: Family?
}

I then made a helper on my Item table, with the following logic:

extension Item {
    static var singleRef: Select<SingleReference, Item, (Plant, Category?, ScientificClassification, Family?)> {
        Item
            .group(by: \.id)
            .join(Plant.all) { item, plant in
                item.plantID.eq(plant.id)
            }
            .leftJoin(Category.all) { _, plant, category in
                plant.categoryID.eq(category.id)
            }
            .join(ScientificClassification.all) { _, plant, _, scientificClassification in
                scientificClassification.plantID.eq(plant.id)
            }
            .leftJoin(Family.all) { _, _, _, scientificClassification, family in
                scientificClassification.familyID.eq(family.id)
            }
            .select {
                SingleReference.Columns(item: $0, plant: $1, category: $2, scientificClassification: $3, family: $4)
            }
    }
}

Now, I'd like to reuse this helper in another query, where I select other values from Space, with the following structure:

@Selection
struct Info {
   let space: Space
   @Column(as [SingleReference].JSONRepresentation.self)
   let singleRefs: [SingleReference]
}

When trying to define the query for such a selection, it seems my helper won't "keep" the SingleReference type and returns a large tuple containing all my joins instead (the returned type is (Item, Plant, Category?, ScientificClassification, Family?)).

The query being this one:

let query = Space
   .join(Item.singleRef) { space, item, plant, category, scientificClassification, family in
       item.spaceID.eq(space.id)
   }
   .select { // The end result I'm trying to achieve, but as seen above, $1 is not of type `SingleReference`
       Info.Columns(space: $0, singleRefs: $1.jsonGroupArray(), numberOfPlants: $2)
   }

Is there a way, or a better fit than Select<…>, to achieve such a query?

[mbrandonw]

Hi @Pomanks, unfortunately this level of query reuse is not yet possible in the library. We would love to support it, but you cannot currently nest a @Selection type inside another @Selection type. You may need to just maintain separate @Selection types that serve each of their purposes instead of trying to compose them together.

[Pomanks]

Hi @mbrandonw, I understand, thank you!