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

huangapple go评论79阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2022年9月30日 20:47:14
  • 转载请务必保留本文链接:https://go.coder-hub.com/73909023.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定