聚合
分组
对 users 按 name
和 age
字段分组,并计算 age 的总和。
package main
import (
"context"
"<project>/ent"
"<project>/ent/user"
)
func Do(ctx context.Context, client *ent.Client) {
var v []struct {
Name string `json:"name"`
Age int `json:"age"`
Sum int `json:"sum"`
Count int `json:"count"`
}
err := client.User.Query().
GroupBy(user.FieldName, user.FieldAge).
Aggregate(ent.Count(), ent.Sum(user.FieldAge)).
Scan(ctx, &v)
}
按单个字段分组.
package main
import (
"context"
"<project>/ent"
"<project>/ent/user"
)
func Do(ctx context.Context, client *ent.Client) {
names, err := client.User.
Query().
GroupBy(user.FieldName).
Strings(ctx)
}
根据边进行分组
如果您想按照自己的逻辑进行聚合,可以使用自定义聚合函数。
下面展示了:如何根据用户的 id
和 name
进行分组,并计算其宠物的平均 age
。
package main
import (
"context"
"log"
"<project>/ent"
"<project>/ent/pet"
"<project>/ent/user"
)
func Do(ctx context.Context, client *ent.Client) {
var users []struct {
ID int
Name string
Average float64
}
err := client.User.Query().
GroupBy(user.FieldID, user.FieldName).
Aggregate(func(s *sql.Selector) string {
t := sql.Table(pet.Table)
s.Join(t).On(s.C(user.FieldID), t.C(pet.OwnerColumn))
return sql.As(sql.Avg(t.C(pet.FieldAge)), "average")
}).
Scan(ctx, &users)
}
Having + Group By
Custom SQL modifiers can be useful if you want to control all query parts. The following shows how to retrieve the oldest users for each role.
package main
import (
"context"
"log"
"entgo.io/ent/dialect/sql"
"<project>/ent"
"<project>/ent/user"
)
func Do(ctx context.Context, client *ent.Client) {
var users []struct {
Id Int
Age Int
Role string
}
err := client.User.Query().
Modify(func(s *sql.Selector) {
s.GroupBy(user.Role)
s.Having(
sql.EQ(
user.FieldAge,
sql.Raw(sql.Max(user.FieldAge)),
),
)
}).
ScanX(ctx, &users)
}
Note: The sql.Raw
is crucial to have. It tells the predicate that sql.Max
is not an arguement.
The above code essentially generates the following SQL query:
SELECT * FROM user GROUP BY user.role HAVING user.age = MAX(user.age)