Use mulitple counts in one query-CodePudding

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?

CodePudding user response：

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 steal the code from the source code and make it actually add more counts, instead of ignoring more than one.