在一个查询中使用多个计数

Question

我有以下结构和查询代码：

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(&singleCound).
Where("timestamp  >= current_timestamp - interval '48 hours' and type = 37").
Count(&singleCount2)

gorm是否在一个查询中提供多个计数？

Answer 1

这是查询生成器的一个常见问题，即这些函数不是很复杂，因此会被重写，而不是添加(根据您的情况(。正如你在源代码中看到的，如果它已经找到了新的计数，它将不会添加新的计数。

您可以通过添加自己的接口函数来研究GORM文档。也许是AddCount()之类的东西，然后从源代码中借用代码，使其实际添加更多计数，而不是忽略多个计数。