英文:
Golang Bun select with group by
问题
I am trying to use Bun in Golang to create a select query, which groups over a state variable and simply counts the elements for each state.
The raw SQL query looks like this:
SELECT state, count(*) FROM "jobs" AS "j" GROUP BY "state"
If I query the database manually by executing this query, I get the following result:
state,count
2,1
1,1
This is exactly as I expect the output to be.
The Go Bun code looks like this:
func FindJobGroups(db *bun.DB, ctx context.Context) (map[string]interface{}, error) {
m := make(map[string]interface{})
err := db.NewSelect().Model((*Job)(nil)).ColumnExpr("state, count(*)").Group("state").Scan(ctx, &m)
return m, err
}
The generated SQL statement (debug) looks exactly as expected:
[bun] 16:18:59.626 SELECT 592µs SELECT state, count(*) FROM "jobs" AS "j" GROUP BY "state"
But the result object m
only contains the count for the first state:
1 = count -> 1
0 = state -> 2
The second state is simply ignored / not contained in the result object.
Is there any way with Bun to perform this query and get the result I am observing in pure SQL?
Additional details:
Job definition:
type Job struct {
bun.BaseModel `bun:"table:jobs,alias:j"`
ID int64 `bun:",pk,autoincrement"`
State int64 `json:"state"`
}
英文:
I am trying to use Bun in Golang to create a select query, which groups over a state variable and simply counts the elements for each state.
The raw sql quey looks like this:
SELECT state, count(*) FROM "jobs" AS "j" GROUP BY "state"
If I quey the database manually by executing this query, i get the following result:
state,count
2,1
1,1
This is exactly as i expect the output to be.
The Go Bun code looks like this:
func FindJobGroups(db *bun.DB, ctx context.Context) (map[string]interface{}, error) {
m := make(map[string]interface{})
err := db.NewSelect().Model((*Job)(nil)).ColumnExpr("state, count(*)").Group("state").Scan(ctx, &m)
return m, err
}
The generated SQL statement (debug) looks exactly as expected:
[bun] 16:18:59.626 SELECT 592µs SELECT state, count(*) FROM "jobs" AS "j" GROUP BY "state"
But the result object m
only contains the count for the first state:
1 = count -> 1
0 = state -> 2
The second state is simply ignored / not contained in the result object.
Is there any way with Bun to perform this query and get the result I am observing in pure SQL?
Additional details:
Job definition:
type Job struct {
bun.BaseModel `bun:"table:jobs,alias:j"`
ID int64 `bun:",pk,autoincrement"`
State int64 `json:"state"`
}
答案1
得分: 1
以下是您要翻译的代码部分:
func FindJobGroups(db *bun.DB, ctx context.Context) ([]map[string]interface{}, error) {
var m []map[string]interface{}
err := db.NewSelect().Model((*Job)(nil)).ColumnExpr("state, count(*)").Group("state").Scan(ctx, &m)
return m, err
}
结果将如下所示:
[
{"state": 2, "count": 1},
{"state": 1, "count": 1}
]
英文:
try
func FindJobGroups(db *bun.DB, ctx context.Context) ([]map[string]interface{}, error) {
var m []map[string]interface{}
err := db.NewSelect().Model((*Job)(nil)).ColumnExpr("state, count(*)").Group("state").Scan(ctx, &m)
return m, err
}
As you need multiple results, so the result destination variable should be a slice.
The result will be like
[
map[state:2 count:1]
map[state:1 count:1]
]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论