Getting aggregates for each row

Hi,

I'm trying to fill the following structure:

    struct LayerWithElements: Decodable, FetchableRecord, Equatable, Hashable {
        let layer: Layer
        let elements: [Element]
        let minX: Double
        let maxX: Double
        let minY: Double
        let maxY: Double
    }

The layer is the main type I'm trying to fetch. Each layer has multiple elements, which I fetch using the association. Each element has a bounding box (stored in a R*Tree table) and I want for each layer the aggregated bounding box over all the layer's elements.

Without the bounding box I can fetch the structure like this just fine:

        let request = Layer.including(all: Layer.elements.order(Element.Columns.id.desc))
        return try LayerWithElements.fetchAll(db, request)

For adding the bounding box logic I tried:

        let bbAlias=TableAlias()
        let request = Layer.including(all: Layer.elements.order(Element.Columns.id.desc))
            .joining(optional: Layer.elements
            .joining(optional: Element.boundingBox.aliased(bbAlias)))
            .annotated(with: bbAlias[min(Column("minX"))].forKey("minX"))
            .annotated(with: bbAlias[max(Column("maxX"))].forKey("maxX"))
            .annotated(with: bbAlias[min(Column("minY"))].forKey("minY"))
            .annotated(with: bbAlias[max(Column("maxY"))].forKey("maxY"))

But the resulting SQL will return only a single row due to the top-level aggregates.

Now I suspect I should add an additional .including() in place of the joins, but that seems to need an association instead of an aggregation. I'm a bit lost here I'm afraid.

Is there a way to get a LayerWithElements structure for each layer, with with the layer's elements as well as its bounding box?

Thanks in advance.

Kind regards,

Remco Poelstra

Hello @remcopoelstra

But the resulting SQL will return only a single row due to the top-level aggregates.

Indeed. You need to group your aggregates. Append .groupByPrimaryKey() to your request.

Not if you don't want to "include" the columns of elements and bounding boxes in the results of the request. That is the difference between including and joining.

In your case, you are only interested by the aggregates, not by association columns. So joining is the proper method.

In your case, you also want to be able to decode the aggregates as top-level properties of the decoded record, so annotated(with:) is also the proper way to grab them.

Is there a way to get a LayerWithElements structure for each layer, with with the layer's elements as well as its bounding box?

Do you want this?

struct LayerWithElements: Decodable, FetchableRecord, Equatable, Hashable {
    let layer: Layer
    let elements: [Element]
    let boundingBox: BoundingBox
}

This boundingBox property does not match the structure of the request. So you add it yourself:

struct LayerWithElements: Decodable, FetchableRecord, Equatable, Hashable {
    let layer: Layer
    let elements: [Element]
    var boundingBox: BoundingBox {
        BoundingBox(minX: minX, maxX: maxX, ...)
    }
    private let minX: Double
    private let maxX: Double
    private let minY: Double
    private let maxY: Double
}

This technique is important, and you'll have plenty of opportunities to reuse it. When the database and the application do not agree on the structure that a type should have, just use private properties for Decodable/GRDB, and expose internal/public properties for the rest of the app. Record types are the proper place to build such facades.

Many thanks!

The .groupByPrimaryKey() indeed did the trick.
I also added the private/public property construct to clean up the interface. Looks much better that way.

Kind regards,

Remco Poelstra

1 Like
Terms of Service

Privacy Policy

Cookie Policy