GORM 基础 -- Gen
https://gorm.io/gen/
github
1、GEN Guides
GEN:友好和更安全的代码生成
1.1 概述
- 来自动态原始SQL的惯用和可重用API
- 100%类型安全的DAO API，不使用
interface{} - Database To Struct遵循GORM约定
- 底层的GORM，支持GORM支持的所有功能，插件，DBMS
1.2 安装
go get -u gorm.io/gen
1.3 Quick start
在你的应用程序中使用gen是非常简单的，下面是它的工作原理:
1.3.1 用golang编写配置
package mainimport "gorm.io/gen"// Dynamic SQL
type Querier interface {// SELECT * FROM @@table WHERE name = @name{{if role !=""}} AND role = @role{{end}}FilterWithNameAndRole(name, role string) ([]gen.T, error)
}func main() {g := gen.NewGenerator(gen.Config{OutPath: "../query",Mode: gen.WithoutContext|gen.WithDefaultQuery|gen.WithQueryInterface, // generate mode})// gormdb, _ := gorm.Open(mysql.Open("root:@(127.0.0.1:3306)/demo?charset=utf8mb4&parseTime=True&loc=Local"))g.UseDB(gormdb) // reuse your gorm db// Generate basic type-safe DAO API for struct `model.User` following conventionsg.ApplyBasic(model.User{})// Generate Type Safe API with Dynamic SQL defined on Querier interface for `model.User` and `model.Company`g.ApplyInterface(func(Querier){}, model.User{}, model.Company{})// Generate the codeg.Execute()
}
1.3.1 Generate Code
go run main.go
1.3.3 在项目中使用生成的代码
import "your_project/query"func main() {// Basic DAO APIuser, err := query.User.Where(u.Name.Eq("modi")).First()// Dynamic SQL APIusers, err := query.User.FilterWithNameAndRole("modi", "admin")
}
2、动态SQL (Dynamic SQL)
Gen允许从原始SQL(Raw SQL)生成完全类型安全的惯用Go代码，它使用在接口上的注释，这些接口可以在代码生成期间应用于多个模型（model）。
不仅您调优的SQL查询，而且SQL片段也允许共享和重用，让我们举一个例子:
2.1 Overview
2.1.1 Raw SQL
type Querier interface {// SELECT * FROM @@table WHERE id=@idGetByID(id int) (gen.T, error) // GetByID query data by id and return it as *struct*// GetUsersByRole query data by roles and return it as *slice of pointer*// (The below blank line is required to comment for the generated method)//// SELECT * FROM @@table WHERE role IN @rolesNameGetByRoles(rolesName ...string) ([]*gen.T, error)// InsertValue insert value//// INSERT INTO @@table (name, age) VALUES (@name, @age)InsertValue(name string, age int) error
}g := gen.NewGenerator(gen.Config{// ... some config
})// Apply the interface to existing `User` and generated `Employee`
g.ApplyInterface(func(Querier) {}, model.User{}, g.GenerateModel("employee"))g.Execute()
运行上面的配置程序为你的应用程序生成查询接口代码，并使用生成的代码如下:
import "your_project/query"func main() {user, err := query.User.GetByID(10)employees, err := query.Employee.GetByRoles("admin", "manager")err := query.User.InsertValue("modi", 18)
}
2.1.2 代码片段
代码段通常与DAO接口一起使用
type Querier interface {// FindByNameAndAge query data by name and age and return it as map//// where("name=@name AND age=@age")FindByNameAndAge(name string, age int) (gen.M, error)
}g := gen.NewGenerator(gen.Config{// ... some config
})// Apply the interface to existing `User` and generated `Employee`
g.ApplyInterface(func(Querier) {}, model.User{}, g.GenerateModel("employee"))g.Execute()
use:
import "your_project/query"func main() {userMap, err := query.User.Where(query.User.Name.Eq("modi")).FilterWithNameAndRole("modi", "admin")
}
2.1.3 More control
Gen支持有条件的注解和自定义返回结果，参考注解了解更多
2.2 注解语法(Annotation Syntax)
注解(Annotation )是在接口的方法上的注释，Gen将解析它们并为应用的结构体生成 查询API。
Gen为动态有条件SQL支持提供了一些约定，让我们从三个方面介绍它们:
- 返回的结果
- 模板占位符（Template Placeholder）
- 模板表达式（Template Expression）
2.2.1 返回的结果
Gen允许配置返回的结果类型，它目前支持以下四种基本类型
| Option | Description |
|---|---|
| gen.T | returns struct |
| gen.M | returns map |
| gen.RowsAffected | returns rowsAffected returned from database (type: int64) |
| error | returns error if any |
e.g:
type Querier interface {// SELECT * FROM @@table WHERE id=@idGetByID(id int) (gen.T, error) // returns struct and error// SELECT * FROM @@table WHERE id=@idGetByID(id int) gen.T // returns data as struct// SELECT * FROM @@table WHERE id=@idGetByID(id int) (gen.M, error) // returns map and error// INSERT INTO @@table (name, age) VALUES (@name, @age)InsertValue(name string, age int) (gen.RowsAffected, error) // returns affected rows count and error
}
这些基本类型可以与其他符号组合，如*，[]，例如:
type Querier interface {// SELECT * FROM @@table WHERE id=@idGetByID(id int) (*gen.T, error) // returns data as pointer and error// SELECT * FROM @@table WHERE id=@idGetByID(id int) (*[]gen.T, error) // returns data as pointer of slice and error// SELECT * FROM @@table WHERE id=@idGetByID(id int) ([]*gen.T, error) // returns data as slice of pointer and error// SELECT * FROM @@table WHERE id=@idGetByID(id int) ([]gen.M, error) // returns data as slice of map and error
}
2.2.2 模板占位符
Gen提供了一些占位符来生成动态和安全的SQL
| Name | Description |
|---|---|
| @@table | escaped & quoted table name |
| @@ | escaped & quoted table/column name from params |
| @ | SQL query params from params |
e.g:
type Filter interface {// SELECT * FROM @@table WHERE @@column=@idFilterWithColumn(column string, value string) (gen.T, error)
}// Apply the `Filter` interface to `User`, `Company`
g.ApplyInterface(func(Filter) {}, model.User{}, model.Company{})
生成代码后，可以在应用程序中像这样使用它：
import "your_project/query"func main() {user, err := query.User.FilterWithColumn("name", "jinzhu")// similar like db.Exec("SELECT * FROM `users` WHERE `name` = ?", "jinzhu")company, err := query.Company.FilterWithColumn("name", "tiktok")// similar like db.Exec("SELECT * FROM `companies` WHERE `name` = ?", "tiktok")
}
2.2.3 表达式模板
Gen为动态条件SQL提供了强大的表达式支持，目前支持以下表达式:
- if/else
- where
- set
- for
if/else
if/else表达式允许使用golang语法作为条件，它可以写成这样:
{{if cond1}}// do something here
{{else if cond2}}// do something here
{{else}}// do something here
{{end}}
例如：
type Querier interface {// SELECT * FROM users WHERE// {{if name !=""}}// username=@name AND// {{end}}// role="admin"QueryWith(name string) (gen.T,error)
}
一个更复杂的例子:
type Querier interface {// SELECT * FROM users// {{if user != nil}}// {{if user.ID > 0}}// WHERE id=@user.ID// {{else if user.Name != ""}}// WHERE username=@user.Name// {{end}}// {{end}}QueryWith(user *gen.T) (gen.T, error)
}
如何使用:
query.User.QueryWith(&User{Name: "zhangqiang"})
// SELECT * FROM users WHERE username="zhangqiang"where
where表达式让你更容易为SQL查询编写where子句，让我们以一个简单的例子为例:
type Querier interface {// SELECT * FROM @@table// {{where}}// id=@id// {{end}}Query(id int) gen.T
}
使用生成的代码，你可以像这样使用它:
query.User.Query(10)
// SELECT * FROM users WHERE id=10这里是另一个复杂的情况，在这种情况下，您将了解到WHERE子句只在任何子表达式匹配时插入，并且它可以巧妙地修剪WHERE子句中不必要的and, or, xor，,。
type Querier interface {// SELECT * FROM @@table// {{where}}// {{if !start.IsZero()}}// created_time > @start// {{end}}// {{if !end.IsZero()}}// AND created_time < @end// {{end}}// {{end}}FilterWithTime(start, end time.Time) ([]gen.T, error)
}
生成的代码可以像这样使用:
var (since = time.Date(2022, 10, 1, 0, 0, 0, 0, time.UTC)end = time.Date(2022, 10, 10, 0, 0, 0, 0, time.UTC)zero = time.Time{}
)query.User.FilterWithTime(since, end)
// SELECT * FROM `users` WHERE created_time > "2022-10-01" AND created_time < "2022-10-10"query.User.FilterWithTime(since, zero)
// SELECT * FROM `users` WHERE created_time > "2022-10-01"query.User.FilterWithTime(zero, end)
// SELECT * FROM `users` WHERE created_time < "2022-10-10"query.User.FilterWithTime(zero, zero)
// SELECT * FROM `users`
set
用于为SQL查询生成set子句的set表达式，它将自动删除不必要的，， 例如:
// UPDATE @@table
// {{set}}
// {{if user.Name != ""}} username=@user.Name, {{end}}
// {{if user.Age > 0}} age=@user.Age, {{end}}
// {{if user.Age >= 18}} is_adult=1 {{else}} is_adult=0 {{end}}
// {{end}}
// WHERE id=@id
Update(user gen.T, id int) (gen.RowsAffected, error)
生成的代码可以像这样使用:
query.User.Update(User{Name: "jinzhu", Age: 18}, 10)
// UPDATE users SET username="jinzhu", age=18, is_adult=1 WHERE id=10query.User.Update(User{Name: "jinzhu", Age: 0}, 10)
// UPDATE users SET username="jinzhu", is_adult=0 WHERE id=10query.User.Update(User{Age: 0}, 10)
// UPDATE users SET is_adult=0 WHERE id=10
for
for表达式遍历一个切片以生成SQL，让我们通过示例进行解释：
// SELECT * FROM @@table
// {{where}}
// {{for _,user:=range user}}
// {{if user.Name !="" && user.Age >0}}
// (username = @user.Name AND age=@user.Age AND role LIKE concat("%",@user.Role,"%")) OR
// {{end}}
// {{end}}
// {{end}}
Filter(users []gen.T) ([]gen.T, error)
使用：
query.User.Filter([]User{{Name: "jinzhu", Age: 18, Role: "admin"},{Name: "zhangqiang", Age: 18, Role: "admin"},{Name: "modi", Age: 18, Role: "admin"},{Name: "songyuan", Age: 18, Role: "admin"},
})
// SELECT * FROM users WHERE
// (username = "jinzhu" AND age=18 AND role LIKE concat("%","admin","%")) OR
// (username = "zhangqiang" AND age=18 AND role LIKE concat("%","admin","%"))
// (username = "modi" AND age=18 AND role LIKE concat("%","admin","%")) OR
// (username = "songyuan" AND age=18 AND role LIKE concat("%","admin","%"))3、DAO
3.1 Overview
Gen遵循配置即代码（Configuration As Code）实践来生成DAO接口，下面是对配置的介绍。
3.1.1 配置
您需要将配置编写为可运行的golang程序，通常，该程序将被组织在应用程序的子目录中。
/ configuration.go
package mainimport ("gorm.io/gen""gorm.io/gorm""gorm.io/driver/sqlite"
)func main() {// Initialize the generator with configurationg := gen.NewGenerator(gen.Config{OutPath: "../dal", // output directory, default value is ./queryMode: gen.WithDefaultQuery | gen.WithQueryInterface,FieldNullable: true,})// Initialize a *gorm.DB instancedb, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})// Use the above `*gorm.DB` instance to initialize the generator,// which is required to generate structs from db when using `GenerateModel/GenerateModelAs`g.UseDB(db)// Generate default DAO interface for those specified structsg.ApplyBasic(model.Customer{}, model.CreditCard{}, model.Bank{}, model.Passport{})// Generate default DAO interface for those generated structs from databasecompanyGenerator := g.GenerateModelAs("company", "MyCompany"),g.ApplyBasic(g.GenerateModel("users"),companyGenerator,g.GenerateModelAs("people", "Person",gen.FieldIgnore("deleted_at"),gen.FieldNewTag("age", `json:"-"`),),)// Execute the generatorg.Execute()
}
运行上面的程序，它将生成代码到目录../dal，你可以在你的应用程序中导入dal包，并使用它的接口来查询数据:
3.1.2 gen.Config
type Config struct {OutPath string // query code pathOutFile string // query code file name, default: gen.goModelPkgPath string // generated model code's package nameWithUnitTest bool // generate unit test for query codeFieldNullable bool // generate pointer when field is nullableFieldCoverable bool // generate pointer when field has default value, to fix problem zero value cannot be assign: https://gorm.io/docs/create.html#Default-ValuesFieldSignable bool // detect integer field's unsigned type, adjust generated data typeFieldWithIndexTag bool // generate with gorm index tagFieldWithTypeTag bool // generate with gorm column type tagMode GenerateMode // generator modes
}
输出选项
| Option Name | Description |
|---|---|
| OutPath | Output destination folder for the generator, default value: ./query |
| OutFile | Query code file name, default value: gen.go |
| ModelPkgPath | Generated DAO package’s package name, default value: model |
| WithUnitTest | Generate unit tests for the DAO package, default value: false |
生成结构体选项
| Option Name | Description |
|---|---|
| FieldNullable | 如果列在数据库中可为空(nullable )，则生成字段类型的指针 |
| FieldCoverable | 如果列在数据库中有默认值，则生成字段类型的指针, 避免零值问题, e.g: https://gorm.io/docs/create.html#Default-Values |
| FieldSignable | 基于列的数据库数据类型,使用有符号类型作为字段类型 |
| FieldWithIndexTag | Generate with gorm index tag |
| FieldWithTypeTag | Generate with gorm type tag, for example: gorm:"type:varchar(12)", default value: false |
请参考数据库到结构体以获取更多选项
生成器模式
| Tag Name | Description |
|---|---|
| gen.WithDefaultQuery | Generate global variable Q as DAO interface, then you can query data like: dal.Q.User.First() |
| gen.WithQueryInterface | 生成查询api接口而不是结构体,通常用于模拟测试 |
| gen.WithoutContext | 在没有上下文约束的情况下生成代码,然后您可以在不使用上下文的情况下查询数据，如: dal.User.First(), or you have to query with the context, e.g: dal.User.WithContext(ctx).First() |
DAO Interface
一个生成DAO查询接口的示例
type IUserDo interface {// CreateCreate(values ...*model.User) errorCreateInBatches(values []*model.User, batchSize int) errorSave(values ...*model.User) error// QueryClauses(conds ...clause.Expression) IUserDoAs(alias string) gen.DaoColumns(cols ...field.Expr) gen.ColumnsNot(conds ...gen.Condition) IUserDoOr(conds ...gen.Condition) IUserDoSelect(conds ...field.Expr) IUserDoWhere(conds ...gen.Condition) IUserDoOrder(conds ...field.Expr) IUserDoDistinct(cols ...field.Expr) IUserDoOmit(cols ...field.Expr) IUserDoJoin(table schema.Tabler, on ...field.Expr) IUserDoLeftJoin(table schema.Tabler, on ...field.Expr) IUserDoRightJoin(table schema.Tabler, on ...field.Expr) IUserDoGroup(cols ...field.Expr) IUserDoHaving(conds ...gen.Condition) IUserDoLimit(limit int) IUserDoOffset(offset int) IUserDoScopes(funcs ...func(gen.Dao) gen.Dao) IUserDoUnscoped() IUserDoPluck(column field.Expr, dest interface{}) errorAttrs(attrs ...field.AssignExpr) IUserDoAssign(attrs ...field.AssignExpr) IUserDoJoins(fields ...field.RelationField) IUserDoPreload(fields ...field.RelationField) IUserDoCount() (count int64, err error)FirstOrInit() (*model.User, error)FirstOrCreate() (*model.User, error)Returning(value interface{}, columns ...string) IUserDoFirst() (*model.User, error)Take() (*model.User, error)Last() (*model.User, error)Find() ([]*model.User, error)FindInBatch(batchSize int, fc func(tx gen.Dao, batch int) error) (results []*model.User, err error)FindInBatches(result *[]*model.User, batchSize int, fc func(tx gen.Dao, batch int) error) errorFindByPage(offset int, limit int) (result []*model.User, count int64, err error)ScanByPage(result interface{}, offset int, limit int) (count int64, err error)Scan(result interface{}) (err error)// UpdateUpdate(column field.Expr, value interface{}) (info gen.ResultInfo, err error)UpdateSimple(columns ...field.AssignExpr) (info gen.ResultInfo, err error)Updates(value interface{}) (info gen.ResultInfo, err error)UpdateColumn(column field.Expr, value interface{}) (info gen.ResultInfo, err error)UpdateColumnSimple(columns ...field.AssignExpr) (info gen.ResultInfo, err error)UpdateColumns(value interface{}) (info gen.ResultInfo, err error)UpdateFrom(q gen.SubQuery) gen.Dao// DeleteDelete(...*model.User) (info gen.ResultInfo, err error)// CommonDebug() IUserDoWithContext(ctx context.Context) IUserDoWithResult(fc func(tx gen.Dao)) gen.ResultInfoReadDB() IUserDoWriteDB() IUserDo
}
3.1.3 使用示例
如果启用了gen.WithDefaultQuery，则使用全局变量Q
import "your_project/dal"func main() {// Initialize a *gorm.DB instancedb, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})dal.SetDefault(db)// query the first useruser, err := dal.Q.User.First()
}
初始化DAO查询接口
import "your_project/dal"var Q dal.Queryfunc main() {// Initialize a *gorm.DB instancedb, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})Q = dal.Use(db)// query the first useruser, err := Q.User.First()
}
如需更多使用详情，请查看下节
- Create
- Update
- Query
- Delete
- Associations
- Transaction
3.2 Create
3.2.1 创建纪录
您可以使用类型安全的Create方法插入记录，该方法在创建数据时只接受当前模型的指针
// u refer to query.user
user := model.User{Name: "Modi", Age: 18, Birthday: time.Now()}u := query.User
err := u.WithContext(ctx).Create(&user) // pass pointer of data to Createerr // returns error
3.2.2 用选定的字段创建记录
您可以在创建数据时使用Select，它只会插入那些选定的字段
u := query.User
u.WithContext(ctx).Select(u.Name, u.Age).Create(&user)
// INSERT INTO `users` (`name`,`age`) VALUES ("modi", 18)ignore fields with Omit:
u := query.User
u.WithContext(ctx).Omit(u.Name, u.Age).Create(&user)
// INSERT INTO `users` (`address`, `birthday`) VALUES ("2021-08-17 20:54:12.000", 18)3.2.3 批量插入
为了有效地插入大量的记录，将一个切片传递给Create方法。GORM将生成一条SQL语句来插入所有数据并回填主键值。
var users = []*model.User{{Name: "modi"}, {Name: "zhangqiang"}, {Name: "songyuan"}}
query.User.WithContext(ctx).Create(users...)for _, user := range users {user.ID // 1,2,3
}
你可以在创建CreateInBatches时指定批大小(batch size )，例如:
var users = []*User{{Name: "modi_1"}, ...., {Name: "modi_10000"}}// batch size 100
query.User.WithContext(ctx).CreateInBatches(users, 100)
如果你也可以在gorm.Config / gorm.Session中设置CreateBatchSize
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{CreateBatchSize: 1000,
})
// OR
db = db.Session(&gorm.Session{CreateBatchSize: 1000})u := query.NewUser(db)var users = []User{{Name: "modi_1"}, ...., {Name: "modi_5000"}}u.WithContext(ctx).Create(&users)
// INSERT INTO users xxx (5 batches)
3.2.4 Upsert / On Conflict
Gen为不同的数据库提供了兼容的Upsert支持
import "gorm.io/gorm/clause"// Do nothing on conflict
err := query.User.WithContext(ctx).Clauses(clause.OnConflict{DoNothing: true}).Create(&user)// Update columns to default value on `id` conflict
err := query.User.WithContext(ctx).Clauses(clause.OnConflict{Columns: []clause.Column{{Name: "id"}},DoUpdates: clause.Assignments(map[string]interface{}{"role": "user"}),
}).Create(&users)
// MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET ***; SQL Server
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE ***; MySQLerr := query.User.WithContext(ctx).Clauses(clause.OnConflict{Columns: []string{"Name", "Age"}}).Create(&user).Error
// MERGE INTO "users" USING *** WHEN NOT MATCHED THEN INSERT *** WHEN MATCHED THEN UPDATE SET "name"="excluded"."name"; SQL Server
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age"; PostgreSQL
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age`=VALUES(age); MySQL// Update all columns, except primary keys, to new value on conflict
err := query.User.WithContext(ctx).Clauses(clause.OnConflict{UpdateAll: true,
}).Create(&users)
// INSERT INTO "users" *** ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name", "age"="excluded"."age", ...;
// INSERT INTO `users` *** ON DUPLICATE KEY UPDATE `name`=VALUES(name),`age`=VALUES(age), ...; MySQL
3.3 Query
3.3.1 检索单个对象
生成的代码提供了First、Take、Last方法来从数据库中检索单个对象，它在查询数据库时添加了LIMIT 1条件，如果没有找到记录，它将返回错误ErrRecordNotFound。
u := query.User// Get the first record ordered by primary key
user, err := u.WithContext(ctx).First()
// SELECT * FROM users ORDER BY id LIMIT 1;// Get one record, no specified order
user, err := u.WithContext(ctx).Take()
// SELECT * FROM users LIMIT 1;// Get last record, ordered by primary key desc
user, err := u.WithContext(ctx).Last()
// SELECT * FROM users ORDER BY id DESC LIMIT 1;// select by write db
user, err := u.WithContext(ctx).WriteDB().Last()// check error ErrRecordNotFound
errors.Is(err, gorm.ErrRecordNotFound)
3.3.2 用主键检索对象
u := query.Useruser, err := u.WithContext(ctx).Where(u.ID.Eq(10)).First()
// SELECT * FROM users WHERE id = 10;users, err := u.WithContext(ctx).Where(u.ID.In(1,2,3)).Find()
// SELECT * FROM users WHERE id IN (1,2,3);
如果主键是一个字符串(例如，像uuid)，查询将被写成如下:
user, err := u.WithContext(ctx).Where(u.ID.Eq("1b74413f-f3b8-409f-ac47-e8c062e3472a")).First()
// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";
3.3.3 检索所有对象
u := query.User// Get all records
users, err := u.WithContext(ctx).Find()
// SELECT * FROM users;
3.3.4 条件(Conditions)
字段查询接口
Gen 为每个字段生成类型安全的接口，可以使用它们生成SQL表达式
| Field Type | Supported Interface |
|---|---|
| generic | IsNull/IsNotNull/Count/Eq/Neq/Gt/Gte/Lt/Lte/Like/Value/Sum/IfNull |
| int | Eq/Neq/Gt/Gte/Lt/Lte/In/NotIn/Between/NotBetween/Like/NotLike/Add/Sub/Mul/Div/Mod/FloorDiv/RightShift/LeftShift/BitXor/BitAnd/BitOr/BitFlip/Value/Zero/Sum/IfNull |
| uint | same with int |
| float | Eq/Neq/Gt/Gte/Lt/Lte/In/NotIn/Between/NotBetween/Like/NotLike/Add/Sub/Mul/Div/FloorDiv/Floor/Value/Zero/Sum/IfNull |
| string | Eq/Neq/Gt/Gte/Lt/Lte/Between/NotBetween/In/NotIn/Like/NotLike/Regexp/NotRegxp/FindInSet/FindInSetWith/Value/Zero/IfNull |
| bool | Not/Is/And/Or/Xor/BitXor/BitAnd/BitOr/Value/Zero |
| time | Eq/Neq/Gt/Gte/Lt/Lte/Between/NotBetween/In/NotIn/Add/Sub/Date/DateDiff/DateFormat/Now/CurDate/CurTime/DayName/MonthName/Month/Day/Hour/Minute/Second/MicroSecond/DayOfWeek/DayOfMonth/FromDays/FromUnixtime/Value/Zero/Sum/IfNull |
下面是一些用法示例:
u := query.User// Get first matched record
user, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).First()
// SELECT * FROM users WHERE name = 'modi' ORDER BY id LIMIT 1;// Get all matched records
users, err := u.WithContext(ctx).Where(u.Name.Neq("modi")).Find()
// SELECT * FROM users WHERE name <> 'modi';// IN
users, err := u.WithContext(ctx).Where(u.Name.In("modi", "zhangqiang")).Find()
// SELECT * FROM users WHERE name IN ('modi','zhangqiang');// LIKE
users, err := u.WithContext(ctx).Where(u.Name.Like("%modi%")).Find()
// SELECT * FROM users WHERE name LIKE '%modi%';// AND
users, err := u.WithContext(ctx).Where(u.Name.Eq("modi"), u.Age.Gte(17)).Find()
// SELECT * FROM users WHERE name = 'modi' AND age >= 17;// Time
users, err := u.WithContext(ctx).Where(u.Birthday.Gt(birthTime).Find()
// SELECT * FROM users WHERE birthday > '2000-01-01 00:00:00';// BETWEEN
users, err := u.WithContext(ctx).Where(u.Birthday.Between(lastWeek, today)).Find()
// SELECT * FROM users WHERE birthday BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';Not 条件
建造Not 条件，类似于Where：
u := query.Useruser, err := u.WithContext(ctx).Not(u.Name.Eq("modi")).First()
// SELECT * FROM users WHERE NOT name = "modi" ORDER BY id LIMIT 1;// Not In
users, err := u.WithContext(ctx).Not(u.Name.In("modi", "zhangqiang")).Find()
// SELECT * FROM users WHERE name NOT IN ("modi", "zhangqiang");// Not In slice of primary keys
user, err := u.WithContext(ctx).Not(u.ID.In(1,2,3)).First()
// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
Or 条件
u := query.Userusers, err := u.WithContext(ctx).Where(u.Role.Eq("admin")).Or(u.Role.Eq("super_admin")).Find()
// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';Group 条件
使用Group 条件更容易编写复杂的SQL查询
p := query.Pizza
pd := p.WithContext(ctx)pizzas, err := pd.Where(pd.Where(p.Pizza.Eq("pepperoni")).Where(pd.Where(p.Size.Eq("small")).Or(p.Size.Eq("medium"))),
).Or(pd.Where(p.Pizza.Eq("hawaiian")).Where(p.Size.Eq("xlarge")),
).Find()// SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")
选择特定字段
Select允许您指定要从数据库检索的字段。否则，GORM将默认选择所有字段。
u := query.Userusers, err := u.WithContext(ctx).Select(u.Name, u.Age).Find()
// SELECT name, age FROM users;u.WithContext(ctx).Select(u.Age.Avg()).Rows()
// SELECT Avg(age) FROM users;
元组查询 (Tuple Query)
u := query.Userusers, err := u.WithContext(ctx).Where(u.WithContext(ctx).Columns(u.ID, u.Name).In(field.Values([][]interface{}{{1, "modi"}, {2, "zhangqiang"}}))).Find()
// SELECT * FROM `users` WHERE (`id`, `name`) IN ((1,'humodi'),(2,'tom'));
JSON Query
u := query.Userusers, err := u.WithContext(ctx).Where(gen.Cond(datatypes.JSONQuery("attributes").HasKey("role"))...).Find()
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`,'$.role') IS NOT NULL;
Order
从数据库检索记录时指定顺序
u := query.Userusers, err := u.WithContext(ctx).Order(u.Age.Desc(), u.Name).Find()
// SELECT * FROM users ORDER BY age DESC, name;// Multiple orders
users, err := u.WithContext(ctx).Order(u.Age.Desc()).Order(u.Name).Find()
// SELECT * FROM users ORDER BY age DESC, name;
通过字符串获取字段
u := query.UserorderCol, ok := u.GetFieldByName(orderColStr) // maybe orderColStr == "id"
if !ok {// User doesn't contains orderColStr
}users, err := u.WithContext(ctx).Order(orderCol).Find()
// SELECT * FROM users ORDER BY age;// OR Desc
users, err := u.WithContext(ctx).Order(orderCol.Desc()).Find()
// SELECT * FROM users ORDER BY age DESC;
Limit & Offset
Limit指定要检索的最大记录数
Offset指定在开始返回记录之前要跳过的记录数
u := query.Userurers, err := u.WithContext(ctx).Limit(3).Find()
// SELECT * FROM users LIMIT 3;// Cancel limit condition with -1
users, err := u.WithContext(ctx).Limit(10).Limit(-1).Find()
// SELECT * FROM users;users, err := u.WithContext(ctx).Offset(3).Find()
// SELECT * FROM users OFFSET 3;users, err := u.WithContext(ctx).Limit(10).Offset(5).Find()
// SELECT * FROM users OFFSET 5 LIMIT 10;// Cancel offset condition with -1
users, err := u.WithContext(ctx).Offset(10).Offset(-1).Find()
// SELECT * FROM users;
Group By & Having
u := query.Uservar users []struct {Name stringTotal int
}
err := u.WithContext(ctx).Select(u.Name, u.ID.Count().As("total")).Group(u.Name).Scan(&users)
// SELECT name, count(id) as total FROM `users` GROUP BY `name`err := u.WithContext(ctx).Select(u.Name, u.Age.Sum().As("total")).Where(u.Name.Like("%modi%")).Group(u.Name).Scan(&users)
// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "%modi%" GROUP BY `name`err := u.WithContext(ctx).Select(u.Name, u.Age.Sum().As("total")).Group(u.Name).Having(u.Name.Eq("group")).Scan(&users)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"rows, err := u.WithContext(ctx).Select(u.Birthday.As("date"), u.Age.Sum().As("total")).Group(u.Birthday).Rows()
for rows.Next() {...
}o := query.Orderrows, err := o.WithContext(ctx).Select(o.CreateAt.Date().As("date"), o.Amount.Sum().As("total")).Group(o.CreateAt.Date()).Having(u.Amount.Sum().Gt(100)).Rows()
for rows.Next() {...
}var results []struct {Date time.TimeTotal int
}o.WithContext(ctx).Select(o.CreateAt.Date().As("date"), o.WithContext(ctx).Amount.Sum().As("total")).Group(o.CreateAt.Date()).Having(u.Amount.Sum().Gt(100)).Scan(&results)Distinct
从模型中选择不同的值
u := query.Userusers, err := u.WithContext(ctx).Distinct(u.Name, u.Age).Order(u.Name, u.Age.Desc()).Find()Distinct 也可以和 Pluck and Count 一起用
Joins
指定连接条件
q := query
u := q.User
e := q.Email
c := q.CreditCardtype Result struct {Name stringEmail stringID int64
}var result Resulterr := u.WithContext(ctx).Select(u.Name, e.Email).LeftJoin(e, e.UserID.EqCol(u.ID)).Scan(&result)
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id// self join
var result Result
u2 := u.As("u2")
err := u.WithContext(ctx).Select(u.Name, u2.ID).LeftJoin(u2, u2.ID.EqCol(u.ID)).Scan(&result)
// SELECT users.name, u2.id FROM `users` left join `users` u2 on u2.id = users.id//join with sub query
var result Result
e2 := e.As("e2")
err := u.WithContext(ctx).Select(u.Name, e2.Email).LeftJoin(e.WithContext(ctx).Select(e.Email, e.UserID).Where(e.UserID.Gt(100)).As("e2"), e2.UserID.EqCol(u.ID)).Scan(&result)
// SELECT users.name, e2.email FROM `users` left join (select email,user_id from emails where user_id > 100) as e2 on e2.user_id = users.idrows, err := u.WithContext(ctx).Select(u.Name, e.Email).LeftJoin(e, e.UserID.EqCol(u.ID)).Rows()
for rows.Next() {...
}var results []Resulterr := u.WithContext(ctx).Select(u.Name, e.Email).LeftJoin(e, e.UserID.EqCol(u.ID)).Scan(&results)// multiple joins with parameter
users := u.WithContext(ctx).Join(e, e.UserID.EqCol(u.id), e.Email.Eq("modi@example.org")).Join(c, c.UserID.EqCol(u.ID)).Where(c.Number.Eq("411111111111")).Find()New Field Expiression
有时您可能需要为动态SQL生成创建一个动态字段
| Field Type | Create Function |
|---|---|
| generic | NewField |
| int | NewInt/NewInt8/…/NewInt64 |
| uint | NewUint/NewUint8/…/NewUint64 |
| float | NewFloat32/NewFloat64 |
| string | NewString/NewBytes |
| bool | NewBool |
| time | NewTime |
用例:
通用字段(Generic Fields)
import "gorm.io/gen/field"// create a new generic field map to `generic_a`
f := field.NewField("table_name", "generic")
// `table_name`.`generic` IS NULL
f.IsNull()// compare fields
id := field.NewField("user", "id")
anotherID := field.NewField("another", "id")
// `user`.`id` = `another`.`id`
id.EqCol(anotherID)
int/uint/float Fields
// int field
f := field.NewInt("user", "id")
// `user`.`id` = 123
f.Eq(123)
// `user`.`id` DESC
f.Desc()
// `user`.`id` AS `user_id`
f.As("user_id")
// COUNT(`user`.`id`)
f.Count()
// SUM(`user`.`id`)
f.Sum()
// SUM(`user`.`id`) > 123
f.Sum().Gt(123)
// ((`user`.`id`+1)*2)/3
f.Add(1).Mul(2).Div(3),
// `user`.`id` <<< 3
f.LeftShift(3)
String Fields
name := field.NewStirng("user", "name")
// `user`.`name` = "modi"
name.Eq("modi")
// `user`.`name` LIKE %modi%
name.Like("%modi%")
// `user`.`name` REGEXP .*
name.Regexp(".*")
// `user`.`name` FIND_IN_SET(`name`,"modi,jinzhu,zhangqiang")
name.FindInSet("modi,jinzhu,zhangqiang")
// `uesr`.`name` CONCAT("[",name,"]")
name.Concat("[", "]")
Time Fields
birth := field.NewStirng("user", "birth")
// `user`.`birth` = ? (now)
birth.Eq(time.Now())
// DATE_ADD(`user`.`birth`, INTERVAL ? MICROSECOND)
birth.Add(time.Duration(time.Hour).Microseconds())
// DATE_FORMAT(`user`.`birth`, "%W %M %Y")
birth.DateFormat("%W %M %Y")
Bool Fields
active := field.NewBool("user", "active")
// `user`.`active` = TRUE
active.Is(true)
// NOT `user`.`active`
active.Not()
// `user`.`active` AND TRUE
active.And(true)
3.3.5 SubQuery
子查询可以嵌套在查询中，GEN可以在使用Dao对象作为参数时生成子查询
o := query.Order
u := query.Userorders, err := o.WithContext(ctx).Where(o.WithContext(ctx).Columns(o.Amount).Gt(o.WithContext(ctx).Select(o.Amount.Avg())).Find()
// SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");subQuery := u.WithContext(ctx).Select(u.Age.Avg()).Where(u.Name.Like("name%"))
users, err := u.WithContext(ctx).Select(u.Age.Avg().As("avgage")).Group(u.Name).Having(u.WithContext(ctx).Columns(u.Age.Avg()).Gt(subQuery).Find()
// SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")// Select users with orders between 100 and 200
subQuery1 := o.WithContext(ctx).Select(o.ID).Where(o.UserID.EqCol(u.ID), o.Amount.Gt(100))
subQuery2 := o.WithContext(ctx).Select(o.ID).Where(o.UserID.EqCol(u.ID), o.Amount.Gt(200))
u.WithContext(ctx).Exists(subQuery1).Not(u.WithContext(ctx).Exists(subQuery2)).Find()
// SELECT * FROM `users` WHERE EXISTS (SELECT `orders`.`id` FROM `orders` WHERE `orders`.`user_id` = `users`.`id` AND `orders`.`amount` > 100 AND `orders`.`deleted_at` IS NULL) AND NOT EXISTS (SELECT `orders`.`id` FROM `orders` WHERE `orders`.`user_id` = `users`.`id` AND `orders`.`amount` > 200 AND `orders`.`deleted_at` IS NULL) AND `users`.`deleted_at` IS NULLFrom SubQuery
GORM允许使用方法Table在FROM子句中使用子查询，例如
u := query.User
p := query.Petusers, err := gen.Table(u.WithContext(ctx).Select(u.Name, u.Age).As("u")).Where(u.Age.Eq(18)).Find()
// SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18subQuery1 := u.WithContext(ctx).Select(u.Name)
subQuery2 := p.WithContext(ctx).Select(p.Name)
users, err := gen.Table(subQuery1.As("u"), subQuery2.As("p")).Find()
db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
// SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p
3.4 Update
3.4.1 更新单列
当使用Update更新单个列时，它需要有任何条件，否则将引发错误ErrMissingWhereClause，例如:
u := query.User// Update with conditions
u.WithContext(ctx).Where(u.Activate.Is(true)).Update(u.Name, "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;// Update with conditions
u.WithContext(ctx).Where(u.Activate.Is(true)).Update(u.Age, u.Age.Add(1))
// or
u.WithContext(ctx).Where(u.Activate.Is(true)).UpdateSimple(u.Age.Add(1))
// UPDATE users SET age=age+1, updated_at='2013-11-17 21:34:10' WHERE active=true;u.WithContext(ctx).Where(u.Activate.Is(true)).UpdateSimple(u.Age.Zero())
// UPDATE users SET age=0, updated_at='2013-11-17 21:34:10' WHERE active=true;
3.4.2 更新多列
Updates支持使用struct或map[string]interface{}进行更新，当使用struct进行更新时，默认只更新非零字段
u := query.User// Update attributes with `map`
u.WithContext(ctx).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;// Update attributes with `struct`
u.WithContext(ctx).Where(u.ID.Eq(111)).Updates(model.User{Name: "hello", Age: 18, Active: false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;// Update with expression
u.WithContext(ctx).Where(u.ID.Eq(111)).UpdateSimple(u.Age.Add(1), u.Number.Add(1))
// UPDATE users SET age=age+1,number=number+1, updated_at='2013-11-17 21:34:10' WHERE id=111;u.WithContext(ctx).Where(u.Activate.Is(true)).UpdateSimple(u.Age.Value(17), u.Number.Zero(), u.Birthday.Null())
// UPDATE users SET age=17, number=0, birthday=NULL, updated_at='2013-11-17 21:34:10' WHERE active=true;
当使用struct更新时，GEN将只更新非零字段，您可能希望使用map更新属性或使用Select指定要更新的字段
3.4.3 更新所选字段
如果要更新所选字段或在更新时忽略某些字段，可以使用Select, Omit
u := query.User// Select with Map
// User's ID is `111`:
u.WithContext(ctx).Select(u.Name).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello' WHERE id=111;u.WithContext(ctx).Omit(u.Name).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;result, err := u.WithContext(ctx).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})result.RowsAffected // affect rows number
err // error
3.4.5 从SubQuery更新
使用SubQuery更新表
u := query.User
c := query.Companyu.WithContext(ctx).Update(u.CompanyName, c.Select(c.Name).Where(c.ID.EqCol(u.CompanyID)))
// UPDATE "users" SET "company_name" = (SELECT name FROM companies WHERE companies.id = users.company_id);u.WithContext(ctx).Where(u.Name.Eq("modi")).Update(u.CompanyName, c.Select(c.Name).Where(c.ID.EqCol(u.CompanyID)))3.4.6 从SubQuery更新多个列
使用SubQuery (用于MySQL)更新多个列:
u := query.User
c := query.Companyua := u.As("u")
ca := u.As("c")ua.WithContext(ctx).UpdateFrom(ca.WithContext(ctx).Select(c.ID, c.Address, c.Phone).Where(c.ID.Gt(100))).
Where(ua.CompanyID.EqCol(ca.ID)).
UpdateSimple(ua.Address.SetCol(ca.Address),ua.Phone.SetCol(ca.Phone),
)
// UPDATE `users` AS `u`,(
// SELECT `company`.`id`,`company`.`address`,`company`.`phone`
// FROM `company` WHERE `company`.`id` > 100 AND `company`.`deleted_at` IS NULL
// ) AS `c`
// SET `u`.`address`=`c`.`address`,`c`.`phone`=`c`.`phone`,`updated_at`='2021-11-11 11:11:11.111'
// WHERE `u`.`company_id` = `c`.`id`
3.5 Delete
3.5.1 删除记录
当删除一条记录时，它需要有任何条件，否则它将引发错误ErrMissingWhereClause，例如:
e := query.Email// Email's ID is `10`
e.WithContext(ctx).Where(e.ID.Eq(10)).Delete()
// DELETE from emails where id = 10;// Delete with additional conditions
e.WithContext(ctx).Where(e.ID.Eq(10), e.Name.Eq("modi")).Delete()
// DELETE from emails where id = 10 AND name = "modi";result, err := e.WithContext(ctx).Where(e.ID.Eq(10), e.Name.Eq("modi")).Delete()result.RowsAffected // affect rows number
err // error
3.5.2 用主键删除
GEN允许使用带有inline 条件的主键删除对象，它与数字一起工作。
u.WithContext(ctx).Where(u.ID.In(1,2,3)).Delete()
// DELETE FROM users WHERE id IN (1,2,3);
3.5.3 批量删除
指定的值没有主值，GEN将执行批量删除，它将删除所有匹配的记录
e := query.Emaile.WithContext(ctx).Where(e.Name.Like("%modi%")).Delete()
// DELETE from emails where email LIKE "%modi%";
3.5.4 Soft Delete
如果你的模型包括一个gorm.DeletedAt字段(包含在gorm.Model中)，它将自动获得软删除能力!
当调用Delete时，记录不会从数据库中删除，但是GORM会将DeletedAt的值设置为当前时间，并且数据不能再用普通的查询方法找到。
// Batch Delete
u.WithContext(ctx).Where(u.Age.Eq(20)).Delete()
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;// Soft deleted records will be ignored when querying
users, err := u.WithContext(ctx).Where(u.Age.Eq(20)).Find()
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;
如果你不想把``gorm.Model`包含进去的话。您也可以启用软删除功能，像这样:
type User struct {ID intDeleted gorm.DeletedAtName string
}
3.5.5 查找软删除记录
你可以用Unscoped找到软删除的记录
users, err := db.WithContext(ctx).Unscoped().Where(u.Age.Eq(20)).Find()
// SELECT * FROM users WHERE age = 20;
3.5.6 永久删除(Delete permanently)
您可以使用Unscoped永久删除匹配的记录
o.WithContext(ctx).Unscoped().Where(o.ID.Eq(10)).Delete()
// DELETE FROM orders WHERE id=10;Delete Associations
如果存在，删除源和参数(source & arguments )之间的关系，只删除引用，不会从DB中删除这些对象。
u := query.Useru.Languages.Model(&user).Delete(&languageZH, &languageEN)u.Languages.Model(&user).Delete([]*Language{&languageZH, &languageEN}...)Delete with Select
在删除记录时，可以删除选中的有has one/has many/many2many关系。例如:
u := query.User// delete user's account when deleting user
u.Select(u.Account).Delete(&user)// delete user's Orders, CreditCards relations when deleting user
db.Select(u.Orders.Field(), u.CreditCards.Field()).Delete(&user)// delete user's has one/many/many2many relations when deleting user
db.Select(field.AssociationsFields).Delete(&user)
3.6 Transaction
使用事务函数的过程如下:
q := query.Use(db)q.Transaction(func(tx *query.Query) error {if _, err := tx.User.WithContext(ctx).Where(tx.User.ID.Eq(100)).Delete(); err != nil {return err}if _, err := tx.Article.WithContext(ctx).Create(&model.User{Name:"modi"}); err != nil {return err}return nil
})
3.6.1 Nested Transactions
GEN支持嵌套事务，你可以回滚在一个大事务范围内执行的操作的子集，例如:
q := query.Use(db)q.Transaction(func(tx *query.Query) error {tx.User.WithContext(ctx).Create(&user1)tx.Transaction(func(tx2 *query.Query) error {tx2.User.WithContext(ctx).Create(&user2)return errors.New("rollback user2") // Rollback user2})tx.Transaction(func(tx3 *query.Query) error {tx3.User.WithContext(ctx).Create(&user3)return nil})return nil
})// Commit user1, user3
3.6.2 手动 Transactions
q := query.Use(db)// begin a transaction
tx := q.Begin()// do some database operations in the transaction (use 'tx' from this point, not 'db')
tx.User.WithContext(ctx).Create(...)// ...// rollback the transaction in case of error
tx.Rollback()// Or commit the transaction
tx.Commit()
例如：
q := query.Use(db)func doSomething(ctx context.Context, users ...*model.User) (err error) {tx := q.Begin()defer func() {if recover() != nil || err != nil {_ = tx.Rollback()}}()err = tx.User.WithContext(ctx).Create(users...)if err != nil {return}return tx.Commit()
}
3.6.3 SavePoint/RollbackTo
GEN提供了SavePoint, RollbackTo来保存点和回滚到一个保存点，例如:
tx := q.Begin()
txCtx = tx.WithContext(ctx)txCtx.User.Create(&user1)tx.SavePoint("sp1")
txCtx.Create(&user2)
tx.RollbackTo("sp1") // Rollback user2tx.Commit() // Commit user1
3.7 关联 (Associations)
GEN会像GORM一样自动保存关联。关系(BelongsTo/HasOne/HasMany/Many2Many)重用GORM的标记(tag)。
该特性目前只支持现有模型。
3.7.1 关系 (Relation)
有四种关系。
const (HasOne RelationshipType = RelationshipType(schema.HasOne) // HasOneRel has one relationshipHasMany RelationshipType = RelationshipType(schema.HasMany) // HasManyRel has many relationshipsBelongsTo RelationshipType = RelationshipType(schema.BelongsTo) // BelongsToRel belongs to relationshipMany2Many RelationshipType = RelationshipType(schema.Many2Many) // Many2ManyRel many to many relationship
)
hasOne正向关联，belongsTo反向关联。
简单的讲就是，没有太大的区别，只是在逻辑上出现的思想的偏差（逻辑的合理性）。
belongsTo：可以理解为属于
hasOne：可以理解为拥有
首先，我们创建两张表。
user表 字段 id name password字段
user_address表 id user_id city字段
不难看出，user_address是基于user扩展出来的表。我们可以说，user拥有user_address的外键，user_address的外键属于user。
User模型中关联user_address表的时候使用hasOne
UserAddress模型中关联user表的时候使用belongsTo
关联现有模型
package model// exist model
type Customer struct {gorm.ModelCreditCards []CreditCard `gorm:"foreignKey:CustomerRefer"`
}type CreditCard struct {gorm.ModelNumber stringCustomerRefer uint
}
GEN将检测模型的关联:
// specify model
g.ApplyBasic(model.Customer{}, model.CreditCard{})// assoications will be detected and converted to code
package querytype customer struct {...CreditCards customerHasManyCreditCards
}type creditCard struct{...
}
关联数据库中的表
关联必须由gen.FieldRelate指定
card := g.GenerateModel("credit_cards")
customer := g.GenerateModel("customers", gen.FieldRelate(field.HasMany, "CreditCards", card, &field.RelateConfig{// RelateSlice: true,GORMTag: "foreignKey:CustomerRefer",}),
)g.ApplyBasic(card, custormer)
GEN将生成带有相关字段的模型:
// customers
type Customer struct {ID int64 `gorm:"column:id;type:bigint(20) unsigned;primaryKey" json:"id"`CreatedAt time.Time `gorm:"column:created_at;type:datetime(3)" json:"created_at"`UpdatedAt time.Time `gorm:"column:updated_at;type:datetime(3)" json:"updated_at"`DeletedAt gorm.DeletedAt `gorm:"column:deleted_at;type:datetime(3)" json:"deleted_at"`CreditCards []CreditCard `gorm:"foreignKey:CustomerRefer" json:"credit_cards"`
}// credit_cards
type CreditCard struct {ID int64 `gorm:"column:id;type:bigint(20) unsigned;primaryKey" json:"id"`CreatedAt time.Time `gorm:"column:created_at;type:datetime(3)" json:"created_at"`UpdatedAt time.Time `gorm:"column:updated_at;type:datetime(3)" json:"updated_at"`DeletedAt gorm.DeletedAt `gorm:"column:deleted_at;type:datetime(3)" json:"deleted_at"`CustomerRefer int64 `gorm:"column:customer_refer;type:bigint(20) unsigned" json:"customer_refer"`
}
如果关联模型已经存在，gen.FieldRelateModel可以帮助您建立它们之间的关联。
customer := g.GenerateModel("customers", gen.FieldRelateModel(field.HasMany, "CreditCards", model.CreditCard{}, &field.RelateConfig{// RelateSlice: true,GORMTag: "foreignKey:CustomerRefer",}),
)g.ApplyBasic(custormer)
Relate Config
type RelateConfig struct {// specify field's typeRelatePointer bool // ex: CreditCard *CreditCardRelateSlice bool // ex: CreditCards []CreditCardRelateSlicePointer bool // ex: CreditCards []*CreditCardJSONTag string // related field's JSON tagGORMTag string // related field's GORM tagNewTag string // related field's new tagOverwriteTag string // related field's tag
}
3.7.2 Operation
跳过自动创建/更新
user := model.User{Name: "modi",BillingAddress: Address{Address1: "Billing Address - Address 1"},ShippingAddress: Address{Address1: "Shipping Address - Address 1"},Emails: []Email{{Email: "modi@example.com"},{Email: "modi-2@example.com"},},Languages: []Language{{Name: "ZH"},{Name: "EN"},},
}u := query.Use(db).Useru.WithContext(ctx).Select(u.Name).Create(&user)
// INSERT INTO "users" (name) VALUES ("jinzhu", 1, 2);u.WithContext(ctx).Omit(u.BillingAddress.Field()).Create(&user)
// Skip create BillingAddress when creating a useru.WithContext(ctx).Omit(u.BillingAddress.Field("Address1")).Create(&user)
// Skip create BillingAddress.Address1 when creating a useru.WithContext(ctx).Omit(field.AssociationFields).Create(&user)
// Skip all associations when creating a user
方法Field将使用.连接一系列字段名。例如:u.BillingAddress.Field("Address1", "Street")等于BillingAddress.Address1.Street
找到关联
找到匹配的关联
u := query.Use(db).Userlanguages, err = u.Languages.Model(&user).Find()找到带条件的关联
q := query.Use(db)
u := q.Userlanguages, err = u.Languages.Where(q.Language.Name.In([]string{"ZH","EN"})).Model(&user).Find()