Closed leotumwattana closed 6 months ago
Hello again @leotumwattana,
Indeed SQLite performs an integer division here.
Is there a way support this at the moment?
Well, not really. Association aggregates such as association.count
are not like other expressions that can easily be wrapped in custom SQL so that we could use "CAST(\(association.count) AS REAL)"
. This issue could count as a feature request.
I could come up with an indirect way:
static func request(questId: UUID) -> some FetchRequest<QuestProgress> {
let baseRequest = Quest
.all()
.filter(id: ...)
.annotated(with:
Quest.activeMission.forKey("active").count,
Quest.totalMission.forKey("total").count)
// Compute the progress in a wrapper request until GRDB can
// cast association counts to doubles. This would make it
// possible to perform the floating-point division right
// from the query interface request above.
// See <https://github.com/groue/GRDB.swift/issues/1514>.
return SQLRequest<QuestProgress>(literal: """
SELECT *, (CAST(activeCount AS REAL) / totalCount) AS \(CodingKeys.progress)
FROM (\(request))
""")
}
I had to wrap the request because SQLite won't allow the select clause to refer to other columns:
sqlite> SELECT 'foo' AS name, name;
Parse error: no such column: name
SELECT 'foo' AS name, name;
^--- error here
Or should I annotate with the two separate counts and then use a computed property to calculate progress?
That would be my recommendation unless you really need to have the SQL produce a pre-computed division, in order to let SQLite sort by progress, for example:
return SQLRequest<QuestProgress>(literal: """
SELECT *, (CAST(activeCount AS REAL) / totalCount) AS \(CodingKeys.progress)
FROM (\(request))
ORDER BY \(CodingKeys.progress) DESC
""")
Hi @groue.
Thanks for sharing the wrapper request
technique. Come to think of it, I was doing something similar elsewhere to combine a bunch of aggregates via CTE. Now that this technique has an official name wrapper request
I can consciously keep it in my toolbox for other stuff.
Please do REAL
LY COUNT
this issue as a feature request. (Sorry, couldn't help the bad pun).
Now that this technique has an official name
wrapper request
I can consciously keep it in my toolbox for other stuff.
This is just name I came up with when writing the sample code, so that your app code would not be too degraded if you happened to copy and paste 😅 But yes this technique can be simpler than CTEs in some occasions 🙂
Please do
REAL
LYCOUNT
this issue as a feature request. (Sorry, couldn't help the bad pun).
Computing a progress by dividing two counts is a very reasonable request.
The feature would not be a way to perform any alteration of association aggregates, but the addition of built-in support for CAST
on aggregates and generally all expressions. It will probably come in the form of a free function, as other similar SQL functions (min
, max
, count
, total
, etc.):
cast(association.count, as: .real)
cast(Column("foo"), as: .text)
let activeCount = Quest.activeMission.count
let totalCount = Quest.totalMission.count
let progress = cast(activeCount, as: .real) / totalCount
return Quest
.all()
.filter(id: ...)
.annotated(with: progress)
.asRequest(of: QuestProgress.self)
I hope this sounds OK to you!
That looks great. 👍
Here we go: https://github.com/groue/GRDB.swift/pull/1515
@leotumwattana, the cast
function was shipped in v6.26.0:
static func request(questId: UUID) -> QueryInterfaceRequest<QuestProgress> {
// Compute progress with a floating-point division
let activeCount = Quest.activeMission.count
let totalCount = Quest.totalMission.count
let progress = cast(activeCount, as: .real) / totalCount
return Quest
.all()
.filter(id: ...)
.annotated(with:progress.forKey(CodingKeys.progress))
.asRequest(of: QuestProgress.self)
}
Hi @groue.
Thanks for the quick turnaround.
I tried out the cast
function. The API is much cleaner.
However, there seems to be a more complex use case where this will not work.
Say I now have an additional record Adventure
:
struct Adventure: TableRecord, ... {
let id: UUID
let title: UUID
static let missions = hasMany(
Quest.self,
using: Quest.adventureForeignKey
)
}
struct AdventureQuestProgresses: FetchableRecord, ... {
let adventure: Adventure
let questProgresses: [QuestProgress]
}
extension AdventureQuestProgresses {
static func request(adventureId: UUID) -> QueryInterfaceRequest<AdventureQuestProgresses> {
let activeCount = Quest.activeMission.count
let totalCount = Quest.totalMission.count
let progress = cast(activeCount, as: .real) / totalCount
return Adventure
.all()
.filter(id: ...)
.including(
all: Adventure
.quests
.annotated(with: progress.forKey("progress")) // 👈 This will cause an error.
.forKey(CodingKeys.questProgresses)
)
.asRequest(of: AdventureQuestProgresses.self)
}
}
Whereas, if I stick with the computed property
method to calculate progress
:
struct QuestProgress: FetchableRecord, ... {
let quest: Quest
private let completedMissionCount: Int
private let totalMissionCount: Int
var progress: Double {
Double(completedMissionCount) / Double(totalMissionCount)
}
}
extension AdventureQuestProgresses {
static func request(adventureId: UUID) -> QueryInterfaceRequest<AdventureQuestProgresses> {
Adventure
.all()
.filter(id: ...)
.including(
all: Adventure
.quests
.annotated(
with:
DBQuest.activeMission.forKey("completedMission").count, // 👈 This works
DBQuest.totalMission.forKey("totalMission").count
)
.forKey(CodingKeys.questProgresses)
)
.asRequest(of: AdventureQuestProgresses.self)
}
}
I'm not sure I understand.
What is the error?
If the code that works uses DBQuest
, why don't you use the same associations to compute the progress?
Step 1: starts from code that works
// works
.annotated(
with:
DBQuest.activeMission.forKey("completedMission").count, // 👈 This works
DBQuest.totalMission.forKey("totalMission").count
)
Step 2: extract local variables
let activeCount = DBQuest.activeMission.forKey("completedMission").count
let totalCount = DBQuest.totalMission.forKey("totalMission").count
...
.annotated(
with:
activeCount,
totalCount
)
Step 3: define the progress in SQL
let activeCount = DBQuest.activeMission.forKey("completedMission").count
let totalCount = DBQuest.totalMission.forKey("totalMission").count
let progress = cast(activeCount, as: .real) / totalCount
...
.annotated(
with:
activeCount,
totalCount,
progress.forKey("progress" /* your coding key here */)
)
Step 4: stop fetching the intermediate values
let activeCount = DBQuest.activeMission.forKey("completedMission").count
let totalCount = DBQuest.totalMission.forKey("totalMission").count
let progress = cast(activeCount, as: .real) / totalCount
...
.annotated(with: progress.forKey("progress" /* your coding key here */))
Step 5: remove the keys of active and total missions since they are not used
let activeCount = DBQuest.activeMission.count
let totalCount = DBQuest.totalMission.count
let progress = cast(activeCount, as: .real) / totalCount
...
.annotated(with: progress.forKey("progress" /* your coding key here */))
What did you do?
Hi @groue.
Thanks again for the great library.
I have another support question.
I would like to cast
association count
tofloating-point
to dofloating-point
division. How might I achieve the below?Is there a way support this at the moment?
Or should I annotate with the two separate counts and then use a computed property to calculate progress?
Many thanks for your help in advance!