【go】ent操作之CRUD与联表查询

1 CRUD

1.1 创建

1.1.1 单条创建

ctx := context.Background()
user,err := db.User.Create().SetClass(classObj).SetName("小红").SetSex(false).SetAge(12).Save(ctx)// SaveX()不会返回error类型
user:= db.User.Create().SetClass(classObj).SetName("小红").SetSex(false).SetAge(12).SaveX(ctx)

1.1.2 批量创建

type studentData struct {Name stringAge  intSex  bool
}data := make([]studentData, 3)data[0].Name = "小明"
data[1].Name = "小刚"
data[2].Name = "小李"data[0].Age = 12
data[1].Age = 13
data[2].Age = 11data[0].Sex = true
data[1].Sex = true
data[2].Sex = falsebulk := make([]*ent.User, len(data))
for i, d := range data{bulk[i] := db.User.Create().SetName(d.Name).SetSex(d.Sex).SetAge(d.Age)
}users, err := db.User.CreateBulk(bulk...).Save(ctx)

Create()的对象不立马保存，而是储存在切片中，之后再统一使用CreateBulk()的方法来批量创建数据

1.2 查找

1.2.1 查询单条 / 条件准确查询

user, err := db.User.Query().Where(user.Age(12)).First(ctx)

1.2.2 查询单条 / 条件模糊查询

user, err := db.User.Query().Where(user.NameContains('明')).First(ctx)

1.2.3 查询单条 / In

names := []string{"amy","mike"}
user, err := db.User.Query().Where(user.NameIn(names...)).First(ctx)

1.2.4 查询全部

users, err := db.User.Query().Where(user.Age(12)).All(ctx)

1.3 更新

err := db.User.Update().SetName("一班").SetLevel(2).Exec(ctx)

1.4 删除

user, err := db.User.Delete().Where(user.Name("小红")).Exec(ctx)

2 联表查询

2.1 O2M（一对多查询）

2.1.1 增加Edge

以User和Order举例，一个用户可以有多个订单的场景下的模型定义

// spec/schema/user.go
package schemaimport ("entgo.io/ent""entgo.io/ent/dialect/entsql""entgo.io/ent/schema/edge""entgo.io/ent/schema/field"
)// User holds the schema definition for the User entity.
type User struct {ent.Schema
}// Fields of the User.
func (User) Fields() []ent.Field {return []ent.Field{...field.String("id").MaxLen(50).Comment("id"),field.String("name").MaxLen(50).Comment("名称"),}
}// Edges of the User.
func (User) Edges() []ent.Edge {return []ent.Edge{edge.To("orders", Order.Type),}
}

// spec/schema/order.go
package schemaimport ("entgo.io/ent""entgo.io/ent/dialect/entsql""entgo.io/ent/schema/edge""entgo.io/ent/schema/field"
)// Order holds the schema definition for the Order entity.
type Order struct {ent.Schema
}// Fields of the Order.
func (Order) Fields() []ent.Field {return []ent.Field{...field.String("product").MaxLen(50).Comment("产品"),field.Int("user_id").Comment("用户ID"),}
}// Edges of the Order.
func (Order) Edges() []ent.Edge {return []ent.Edge{edge.From("user", User.Type).Ref("orders").Unique().Field("user_id").Required(),}
}

2.1.2 查询方法

2.1.2.1 查询函数QueryOrder()与WithOrder()

ctx := context.Background()// 查询user下的所有orders
userID := 1
orders, _ := db.User.Query().Where(user.ID(userID)).QueryOrder().All(ctx)// 查询user，同时获取到user下的orders列表
user, _ := db.User.Query().Where(user.ID(userID)).WithOrder().Only(ctx)
orders := user.Edges.Orders// 查询user，同时获取到user下符合条件的orders列表
user, _ := db.User.Query().Where(user.ID(userID)).WithOrder().Where(order.Product("xxx")).Only(ctx)
orders := user.Edges.Orders// 查询order对应的user
orderID := 1
user, _ := db.Order.Query().Where(order.ID(orderID)).QueryUser().Only(ctx)// 查询order，同时获取order对应的user信息
order, _ := db.Order.Query().Where(order.ID(orderID)).WithUser().Only(ctx)
user := order.Edges.User

2.1.2.2 聚合函数 Aggregate求max \ min \ avg

type UserTest struct {CompanyId string  `json:"company_id,omitempty"`Max       int     `json:"max,omitempty"`Min       int     `json:"min"`Avg       float32 `json:"avg,omitempty"`
}res := []UserTest {}
err := db.User.Query().Where(user.CompanyIDIn(companyIds...)).GroupBy(user.FieldCompanyID).Aggregate(func(s *sql.Selector) string {t := sql.Table(order.Table)s.Join(t).On(s.C(user.FieldID), t.C(order.FieldUserID))return sql.As(sql.Max(t.C(order.FieldBandwidth)), "max")},func(s *sql.Selector) string {t := sql.Table(order.Table)s.Join(t).On(s.C(user.FieldID), t.C(order.FieldUserID))return sql.As(sql.Min(t.C(order.FieldBandwidth)), "min")},func(s *sql.Selector) string {t := sql.Table(order.Table)s.Join(t).On(s.C(user.FieldID), t.C(order.FieldUserID))return sql.As(sql.Avg(t.C(order.FieldBandwidth)), "avg")},).Scan(c, &res)

输出：

[{"company_id": "123","max": 100,"min": 10,"avg": 55,},{"company_id": "456","max": 100,"min": 10,"avg": 55,}
]

2.1.2.3 聚合函数 Aggregate根据用户身份做分类统计

type UserTest struct {Count     int     `json:"count,omitempty"`Type      string  `json:"type,omitempty"`
}res := []UserTest {}
err := db.User.Query().Where(user.CompanyIDIn(companyIds...)).Aggregate(ent.Count(),func(s *sql.Selector) string {s.GroupBy(user.FieldJob)return sql.As(user.FieldJob, "type")},).Scan(c, &res)

输出：

[{"type": "teacher","count": 24},{"type": "student","count": 65,}
]

3 Reference

1. ent官方文档：https://entgo.io/docs/getting-started

2. go语言ORM框架ent使用教程：
   https://blog.csdn.net/a914541185/article/details/121274321

3. 在线SQL转Schema：https://old.printlove.cn/tools/sql2ent