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
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.
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.
Thanks! Us usual your suggestion works great!
On a side note, what's your preferred way of receiving donations?
1 Like
You're welcome! And thank you for willing to contribute to your favorite SQLite library! Please check out Sponsor @groue on GitHub Sponsors · GitHub
Cool, I'll check it out soon.