Golang 中的 Bun 选择与分组按照如下方式进行操作:

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

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]
]

huangapple
  • 本文由 发表于 2023年8月4日 22:47:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/76836979.html
匿名

发表评论

匿名网友

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

确定