GORM 基础 -- Gen

皇甫飞跃

2023-12-01

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 main

import "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 conventions
  g.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 code
  g.Execute()
}

1.3.1 Generate Code

go run main.go

1.3.3 在项目中使用生成的代码

import "your_project/query"

func main() {
  // Basic DAO API
  user, err := query.User.Where(u.Name.Eq("modi")).First()

  // Dynamic SQL API
  users, 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=@id
  GetByID(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 @rolesName
  GetByRoles(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=@id
  GetByID(id int) (gen.T, error) // returns struct and error

  // SELECT * FROM @@table WHERE id=@id
  GetByID(id int) gen.T // returns data as struct

  // SELECT * FROM @@table WHERE id=@id
  GetByID(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=@id
  GetByID(id int) (*gen.T, error) // returns data as pointer and error

  // SELECT * FROM @@table WHERE id=@id
  GetByID(id int) (*[]gen.T, error) // returns data as pointer of slice and error

  // SELECT * FROM @@table WHERE id=@id
  GetByID(id int) ([]*gen.T, error) // returns data as slice of pointer and error

  // SELECT * FROM @@table WHERE id=@id
  GetByID(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=@id
  FilterWithColumn(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=10

query.User.Update(User{Name: "jinzhu", Age: 0}, 10)
// UPDATE users SET username="jinzhu", is_adult=0 WHERE id=10

query.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 main

import (
  "gorm.io/gen"
  "gorm.io/gorm"
  "gorm.io/driver/sqlite"
)

func main() {
  // Initialize the generator with configuration
  g := gen.NewGenerator(gen.Config{
     OutPath: "../dal", // output directory, default value is ./query
     Mode:    gen.WithDefaultQuery | gen.WithQueryInterface,
     FieldNullable: true,
  })

  // Initialize a *gorm.DB instance
  db, 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 structs
  g.ApplyBasic(model.Customer{}, model.CreditCard{}, model.Bank{}, model.Passport{})

  // Generate default DAO interface for those generated structs from database
  companyGenerator := g.GenerateModelAs("company", "MyCompany"),
  g.ApplyBasic(
    g.GenerateModel("users"),
    companyGenerator,
    g.GenerateModelAs("people", "Person",
      gen.FieldIgnore("deleted_at"),
      gen.FieldNewTag("age", `json:"-"`),
    ),
  )

  // Execute the generator
  g.Execute()
}

运行上面的程序，它将生成代码到目录../dal，你可以在你的应用程序中导入dal包，并使用它的接口来查询数据:

3.1.2 gen.Config

type Config struct {
  OutPath      string // query code path
  OutFile      string // query code file name, default: gen.go
  ModelPkgPath string // generated model code's package name
  WithUnitTest bool   // generate unit test for query code

  FieldNullable     bool // generate pointer when field is nullable
  FieldCoverable    bool // generate pointer when field has default value, to fix problem zero value cannot be assign: https://gorm.io/docs/create.html#Default-Values
  FieldSignable     bool // detect integer field's unsigned type, adjust generated data type
  FieldWithIndexTag bool // generate with gorm index tag
  FieldWithTypeTag  bool // generate with gorm column type tag

  Mode 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 {
  // Create
  Create(values ...*model.User) error
  CreateInBatches(values []*model.User, batchSize int) error
  Save(values ...*model.User) error

  // Query
  Clauses(conds ...clause.Expression) IUserDo
  As(alias string) gen.Dao
  Columns(cols ...field.Expr) gen.Columns
  Not(conds ...gen.Condition) IUserDo
  Or(conds ...gen.Condition) IUserDo
  Select(conds ...field.Expr) IUserDo
  Where(conds ...gen.Condition) IUserDo
  Order(conds ...field.Expr) IUserDo
  Distinct(cols ...field.Expr) IUserDo
  Omit(cols ...field.Expr) IUserDo
  Join(table schema.Tabler, on ...field.Expr) IUserDo
  LeftJoin(table schema.Tabler, on ...field.Expr) IUserDo
  RightJoin(table schema.Tabler, on ...field.Expr) IUserDo
  Group(cols ...field.Expr) IUserDo
  Having(conds ...gen.Condition) IUserDo
  Limit(limit int) IUserDo
  Offset(offset int) IUserDo
  Scopes(funcs ...func(gen.Dao) gen.Dao) IUserDo
  Unscoped() IUserDo
  Pluck(column field.Expr, dest interface{}) error
  Attrs(attrs ...field.AssignExpr) IUserDo
  Assign(attrs ...field.AssignExpr) IUserDo
  Joins(fields ...field.RelationField) IUserDo
  Preload(fields ...field.RelationField) IUserDo

  Count() (count int64, err error)
  FirstOrInit() (*model.User, error)
  FirstOrCreate() (*model.User, error)
  Returning(value interface{}, columns ...string) IUserDo

  First() (*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) error
  FindByPage(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)

  // Update
  Update(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

  // Delete
  Delete(...*model.User) (info gen.ResultInfo, err error)

  // Common
  Debug() IUserDo
  WithContext(ctx context.Context) IUserDo
  WithResult(fc func(tx gen.Dao)) gen.ResultInfo

  ReadDB() IUserDo
  WriteDB() IUserDo
}

3.1.3 使用示例

如果启用了gen.WithDefaultQuery，则使用全局变量Q

import "your_project/dal"

func main() {
  // Initialize a *gorm.DB instance
  db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})

  dal.SetDefault(db)

  // query the first user
  user, err := dal.Q.User.First()
}

初始化DAO查询接口

import "your_project/dal"

var Q dal.Query

func main() {
  // Initialize a *gorm.DB instance
  db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})

  Q = dal.Use(db)

  // query the first user
  user, err := Q.User.First()
}

如需更多使用详情，请查看下节

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 Create

err // 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 ***; MySQL

err := 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.User

user, 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.User

user, 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.User

users, 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.User

users, 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.User

users, 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.User

users, 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.User

users, 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.User

orderCol, 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.User

urers, 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.User

var users []struct {
    Name  string
    Total 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.Order

rows, 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.Time
    Total 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.User

users, 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.CreditCard

type Result struct {
    Name  string
    Email string
    ID    int64
}

var result Result

err := 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.id

rows, err := u.WithContext(ctx).Select(u.Name, e.Email).LeftJoin(e, e.UserID.EqCol(u.ID)).Rows()
for rows.Next() {
  ...
}

var results []Result

err := 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.User

orders, 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 NULL

From SubQuery

GORM允许使用方法Table在FROM子句中使用子查询，例如

u := query.User
p := query.Pet

users, 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` = 18

subQuery1 := 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.Company

u.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.Company

ua := 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.Email

e.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      int
    Deleted gorm.DeletedAt
    Name    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.User

u.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 user2

tx.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 relationship
    HasMany   RelationshipType = RelationshipType(schema.HasMany)   // HasManyRel has many relationships
    BelongsTo RelationshipType = RelationshipType(schema.BelongsTo) // BelongsToRel belongs to relationship
    Many2Many 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.Model
    CreditCards []CreditCard `gorm:"foreignKey:CustomerRefer"`
}

type CreditCard struct {
    gorm.Model
    Number        string
    CustomerRefer uint
}

GEN将检测模型的关联:

// specify model
g.ApplyBasic(model.Customer{}, model.CreditCard{})

// assoications will be detected and converted to code 
package query

type 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 type
    RelatePointer      bool // ex: CreditCard  *CreditCard
    RelateSlice        bool // ex: CreditCards []CreditCard
    RelateSlicePointer bool // ex: CreditCards []*CreditCard

    JSONTag      string // related field's JSON tag
    GORMTag      string // related field's GORM tag
    NewTag       string // related field's new tag
    OverwriteTag 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).User

u.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 user

u.WithContext(ctx).Omit(u.BillingAddress.Field("Address1")).Create(&user)
// Skip create BillingAddress.Address1 when creating a user

u.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).User

languages, err = u.Languages.Model(&user).Find()

找到带条件的关联

q := query.Use(db)
u := q.User

languages, err = u.Languages.Where(q.Language.Name.In([]string{"ZH","EN"})).Model(&user).Find()