英文:
Use mulitple counts in one query
问题
我有以下的结构体和查询代码:
type Result struct {
StartedLast15Minutes int `gorm:"column:started_last_15_minutes"`
StartedLast60Minutes int `gorm:"column:started_last_60_minutes"`
StartedLast24Hours int `gorm:"column:started_last_24_hours"`
StartedLast48Hours int `gorm:"column:started_last_48_hours"`
FinishedLast15Minutes int `gorm:"column:finished_last_15_minutes"`
FinishedLast60Minutes int `gorm:"column:finished_last_60_minutes"`
FinishedLast24Hours int `gorm:"column:finished_last_24_hours"`
FinishedLast48Hours int `gorm:"column:finished_last_48_hours"`
}
statsResult := Result{}
err = g.db.
Raw(`
SELECT
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '15 minutes' and type = 1 ) as started_last_15_minutes,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '1 hour' and type = 1 ) as started_last_60_minutes,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '24 hours' and type = 1 ) as started_last_24_hours,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '48 hours' and type = 1 ) as started_last_48_hours,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '15 minutes' and type = 37 ) as finished_last_15_minutes,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '1 hour' and type = 37 ) as finished_last_60_minutes,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '24 hours' and type = 37 ) as finished_last_24_hours,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '48 hours' and type = 37 ) as finished_last_48_hours
FROM onboarding_events as oe
`).
Scan(&statsResult).
Error
但是如果我想将其转换为gorm语法,我只能找到使用多个查询的解决方案:
g.db.
Model(model.OnboardingEvent{}).
Where("timestamp >= current_timestamp - interval '24 hours' and type = 37").
Count(&singleCount).
Where("timestamp >= current_timestamp - interval '48 hours' and type = 37").
Count(&singleCount2)
gorm是否提供在一个查询中进行多个计数的功能?
英文:
I have the following struct and query code:
type Result struct {
StartedLast15Minutes int `gorm:"column:started_last_15_minutes"`
StartedLast60Minutes int `gorm:"column:started_last_60_minutes"`
StartedLast24Hours int `gorm:"column:started_last_24_hours"`
StartedLast48Hours int `gorm:"column:started_last_48_hours"`
FinishedLast15Minutes int `gorm:"column:finished_last_15_minutes"`
FinishedLast60Minutes int `gorm:"column:finished_last_60_minutes"`
FinishedLast24Hours int `gorm:"column:finished_last_24_hours"`
FinishedLast48Hours int `gorm:"column:finished_last_48_hours"`
}
statsResult := Result{}
err = g.db.
Raw(`
SELECT
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '15 minutes' and type = 1 ) as started_last_15_minutes,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '1 hour' and type = 1 ) as started_last_60_minutes,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '24 hours' and type = 1 ) as started_last_24_hours,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '48 hours' and type = 1 ) as started_last_48_hours,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '15 minutes' and type = 37 ) as finished_last_15_minutes,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '1 hour' and type = 37 ) as finished_last_60_minutes,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '24 hours' and type = 37 ) as finished_last_24_hours,
COUNT(*) filter (where oe.timestamp >= current_timestamp - interval '48 hours' and type = 37 ) as finished_last_48_hours
FROM onboarding_events as oe
`).
Scan(&statsResult).
Error
But if I want to translate this into gorm syntax I can only find such a solution with multiple queries:
g.db.
Model(model.OnboardingEvent{}).
Where("timestamp >= current_timestamp - interval '24 hours' and type = 37").
Count(&singleCound).
Where("timestamp >= current_timestamp - interval '48 hours' and type = 37").
Count(&singleCount2)
Does gorm offer multiple counts in one query?
答案1
得分: 2
这是一个常见的问题,查询构建器的这些函数并不是非常复杂,因此会覆盖已有的计数,而不是像你在这种情况下所期望的那样进行累加。你可以查看源代码,如果已经找到一个计数,它就不会添加新的计数。
你可以通过添加自己的接口函数来研究 GORM 文档。也许可以像 AddCount()
这样命名,并从源代码中借用代码,使其实际上能够添加更多的计数,而不是忽略多个计数。
英文:
This is a common problem with query builders that these functions are not very complex and thus override, instead of being additive (as you want in your situation). As you can see in the source code it will not add new counts if it already found one.
You can investigate the GORM documentation by adding your own interfaced function. Maybe something like AddCount()
and then borrow the code from the source code and make it actually add more counts, instead of ignoring more than one.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论