1

Hi,

I'm trying to fetch rows with the following structures:

    struct ImageWithErrorCount: Decodable, FetchableRecord, Equatable, Hashable {
        let imageFile: ImageFile
        let errorCount: Int
    }
    struct LayerImagesListItem: Decodable, FetchableRecord, Equatable, Hashable {
        let layer: Layer
        let imageFiles: [ImageWithErrorCount]
    }

I'm doing this with the following request:

            let alias=TableAlias()
            let request = Layer.including(all: Layer.imageFiles.order(ImageFile.Columns.url)
                .joining(optional: ImageFile.errors.aliased(alias))
                .annotated(with: alias[count(Column("code"))].forKey("errorCount"))
            )

This works, up to the point that I want an errorCount for each ImageFile but currently the aggregate groups everything into a single ImageFileWithErrorCount. But it doesn't matter where I try to stick in a .groupByPrimaryKey(), I always get a warning:

Referencing instance method 'groupByPrimaryKey()' on 'TableRequest' requires that 'HasManyAssociation<Layer, ImageFile>' conform to 'AggregatingRequest'

Where or how should I add the .groupByPrimaryKey() to make sure I get an array of imageFiles and errorCounts?

Thanks in advance.

Kind regards,

Remco Poelstra

2

Hello @remcopoelstra,

You did attempt to use groupByPrimaryKey() on Layer.imageFiles, which is an association. And the compiler complains.

First, your attempt is perfectly reasonable! Associations included with including(all:) are executed as distinct SQL requests, and nothing prevents them from being grouped.

Unfortunately, I did not foresee this scenario: that feature is not implemented. Grouping methods are currently only available on requests such as Layer.all(), not on associations like Layer.hasMany(ImageFile.self).

I'm afraid that you have to perform the including(all:) yourself. This should give something like:

// Fetch layers
let layers = try Layer.fetchAll(db)

// Fetch related images
let layerIds = layers.map(\.id)
let imageFiles = try ImageFile
    .filter(layerIds.contains(ImageFile.Columns.layerId)
    .order(ImageFile.Columns.url)
    .annotated(with: ImageFile.errors.count)
    .asRequest(of: ImageWithErrorCount.self)
    .fetchAll(db)

// Group images by layer id
let groupedImageFiles = Dictionary(grouping: imageFiles, by: \.layerId)

// Gather layers and their images together
let items = layers.map { layer in
    LayerImagesListItem(
        layer: layer,
        imageFiles: groupedImageFiles[layer.id] ?? [])
}

This is more verbose, for sure, but this will provide the results you expect. This is more or less how GRDB implements including(all:).

You can consider this thread as a feature request! This is not a trivial one, because grouping methods should only be allowed on associations when they feed including(all:) (not when they feed including(optional:) or including(:required)). I'm not sure the current type hierarchy will make this easy.

EDIT: updated the imageFiles request so that it is annotated with the ImageFile.errors.count aggregate instead of an explicit join + annotation + grouping.

3

Another option would be to allow grouping clauses, having clauses, and aggregate annotations on all to-many associations (maybe even all associations), regardless of how they are included or joined. But this would require a closer study, in order to make sure it makes sense, and allows expressing useful requests. Not so easy either.

4

Thanks! Us usual your suggestion works great!

On a side note, what's your preferred way of receiving donations?

1 Like
5

You're welcome! And thank you for willing to contribute to your favorite SQLite library! Please check out Sponsor @groue on GitHub Sponsors · GitHub

6

@remcopoelstra, the next GRDB release will support association grouping (and much more): Complete Associations and the DerivableRequest Protocol by groue · Pull Request #936 · groue/GRDB.swift · GitHub

Patience is a virtue :wink:

7

Cool, I'll check it out soon.