GORM高级查询

GORM高级查询

一、智能选择字段

GORM 允许通过 Select 方法选择特定的字段，如果您在应用程序中经常使用此功能，你也可以定义一个较小的结构体，以实现调用 API 时自动选择特定的字段，例如：

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

type CreateUsers struct {
	gorm.Model
	Name         string
	Email        *string
	Age          uint8
	Birthday     *time.Time
	MemberNumber sql.NullString
	ActivatedAt  sql.NullTime
	CreatedAt    time.Time
	UpdatedAt    time.Time
	CreditCard   CreditCard `gorm:"foreignKey:ID"`
    
    // 假设后面还有几百个字段...
}
type APIUser struct {
  ID   uint
  Name string
}

// 查询时会自动选择 `id`, `name` 字段
var apiUser []APIUser
//  SELECT `create_users`.`id`,`create_users`.`name` FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL LIMIT 10
db.Debug().Model(&CreateUsers{}).Limit(10).Find(&apiUser)
for _, u := range apiUser{
    fmt.Println(u.ID, u.Name)
}	

注意 QueryFields 模式会根据当前 model 的所有字段名称进行 select。

// 全局配置
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
  QueryFields: true, 
})

db.Find(&user)
// SELECT `users`.`name`, `users`.`age`, ... FROM `users` // 带上这个选项

// 局部配置
// Session Mode

var users []CreateUsers
// SELECT  `create_users`.`id`,`create_users`.`created_at`,`create_users`.`updated_at`,`create_users`.`deleted_at`,`create_users`.`name`,`create_users`.`email`,`create_users`.`age`,`create_users`.`birthday`,`create_users`.`member_number`,`create_users`.`activated_at` FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL
// 查询所有字段
db.Debug().Session(&gorm.Session{QueryFields: true}).Find(&users)
for _, u := range users{
    fmt.Println(u.Name, u.Age, u.Birthday, u.CreatedAt)
}
// SELECT `users`.`name`, `users`.`age`, ... FROM `users`

二、Locking (FOR UPDATE)

GORM 支持多种类型的锁，例如：

db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)
// SELECT * FROM `users` FOR UPDATE

db.Clauses(clause.Locking{
  Strength: "SHARE",
  Table: clause.Table{Name: clause.CurrentTable},
}).Find(&users)
// SELECT * FROM `users` FOR SHARE OF `users`

查看 原生 SQL 及构造器 获取详情

三、子查询

子查询可以嵌套在查询中，GORM 允许在使用 *gorm.DB 对象作为参数时生成子查询

var users []CreateUsers
// SELECT * FROM `create_users` WHERE age > (SELECT AVG(age) FROM `create_users`) AND `create_users`.`deleted_at` IS NULL
// 嵌套子查询
db.Debug().Where("age > (?)", db.Debug().Table("create_users").Select("AVG(age)")).Find(&users)
for _, u := range users {
    fmt.Println(u.Name, u.Age, u.Birthday, u.CreatedAt)
}

type Result struct {
    names string
    Total float64
    Date  *time.Time
}
var result []Result
//  SELECT AVG(age) as Total FROM `create_users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `create_users` WHERE name LIKE 'Randy%')

// 注意查询结果的类型

subQuery := db.Debug().Select("AVG(age)").Where("name LIKE ?", "Randy%").Table("create_users")
db.Debug().Select("AVG(age) as Total").Group("name").Having("AVG(age) > (?)", subQuery).Table("create_users").Find(&result)

for _, r := range result{
    fmt.Println(r.Total)
}

// 查寻获取结果2
subQuery := db.Debug().Select("AVG(age)").Where("name LIKE ?", "Randy%").Table("create_users")
rows, err := db.Select("AVG(age) as Total").Group("name").Having("AVG(age) > (?)", subQuery).Table("create_users").Rows()
	
for rows.Next() {
    var r Result
    err := rows.Scan(&r.Total)
    if err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Printf("Total: %d\n", r.Total)
}

四、From 子查询

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

var users []CreateUsers

// SELECT * FROM (SELECT `name`,`age`,`deleted_at` FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL) as u WHERE age = 18 AND `u`.`deleted_at` IS NULL

db.Debug().Table("(?) as u", db.Model(&CreateUsers{}).Select("name", "age", "deleted_at")).Where("age = ?", 18).Find(&users)

for _, u := range users {
    fmt.Println(u.Name, u.Age, u.Birthday, u.CreatedAt)
}

subQuery1 := db.Model(&User{}).Select("name")
subQuery2 := db.Model(&Pet{}).Select("name")
db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
// SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p


var users []CreateUsers
subQuery1 := db.Model(&CreateUsers{}).Select("name", "deleted_at")
subQuery2 := db.Model(&CreateUsers{}).Select("name", "deleted_at")
// 	SELECT * FROM (SELECT `name`,`deleted_at` FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL) as u, (SELECT `name`,`deleted_at` FROM `create_users` WHERE `create_users`.`deleted_at` IS NULL) as p WHERE `u`.`deleted_at` IS NULL
db.Debug().Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&users)

for _, u := range users {
    fmt.Println(u.Name, u.Age, u.Birthday, u.CreatedAt)
}

五、Group 条件

使用 Group 条件可以更轻松的编写复杂 SQL

db.Where(
    db.Where("pizza = ?", "pepperoni").Where(db.Where("size = ?", "small").Or("size = ?", "medium")),
).Or(
    db.Where("pizza = ?", "hawaiian").Where("size = ?", "xlarge"),
).Find(&Pizza{}).Statement

// SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")

六、IN with multiple columns

Selecting IN with multiple columns

db.Where("(name, age, role) IN ?", [][]interface{}{{"jinzhu", 18, "admin"}, {"jinzhu2", 19, "user"}}).Find(&users)
// SELECT * FROM users WHERE (name, age, role) IN (("jinzhu", 18, "admin"), ("jinzhu 2", 19, "user"));

七、Named Argument

GORM supports named arguments with sql.NamedArg or map[string]interface{}{}, for example:

db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)
// SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"

db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu"}).First(&user)
// SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu" ORDER BY `users`.`id` LIMIT 1

Check out Raw SQL and SQL Builder for more detail

八、Find To Map

GORM allows scan result to map[string]interface{} or []map[string]interface{}, don’t forget to specify Model or Table, for example:

result := map[string]interface{}{}
db.Model(&User{}).First(&result, "id = ?", 1)

var results []map[string]interface{}
db.Table("users").Find(&results)

九、FirstOrInit

Get first matched record or initialize a new instance with given conditions (only works with struct or map conditions)

// User not found, initialize it with give conditions
db.FirstOrInit(&user, User{Name: "non_existing"})
// user -> User{Name: "non_existing"}

// Found user with `name` = `jinzhu`
db.Where(User{Name: "jinzhu"}).FirstOrInit(&user)
// user -> User{ID: 111, Name: "Jinzhu", Age: 18}

// Found user with `name` = `jinzhu`
db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})
// user -> User{ID: 111, Name: "Jinzhu", Age: 18}

initialize struct with more attributes if record not found, those Attrs won’t be used to build SQL query

// User not found, initialize it with give conditions and Attrs
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// user -> User{Name: "non_existing", Age: 20}

// User not found, initialize it with give conditions and Attrs
db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// user -> User{Name: "non_existing", Age: 20}

// Found user with `name` = `jinzhu`, attributes will be ignored
db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "Jinzhu", Age: 18}

Assign attributes to struct regardless it is found or not, those attributes won’t be used to build SQL query and the final data won’t be saved into database

// User not found, initialize it with give conditions and Assign attributes
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
// user -> User{Name: "non_existing", Age: 20}

// Found user with `name` = `jinzhu`, update it with Assign attributes
db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 20}).FirstOrInit(&user)
// SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "Jinzhu", Age: 20}

十、FirstOrCreate

Get first matched record or create a new one with given conditions (only works with struct, map conditions)

// User not found, create a new record with give conditions
db.FirstOrCreate(&user, User{Name: "non_existing"})
// INSERT INTO "users" (name) VALUES ("non_existing");
// user -> User{ID: 112, Name: "non_existing"}

// Found user with `name` = `jinzhu`
db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user)
// user -> User{ID: 111, Name: "jinzhu", "Age": 18}

Create struct with more attributes if record not found, those Attrs won’t be used to build SQL query

// User not found, create it with give conditions and Attrs
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
// user -> User{ID: 112, Name: "non_existing", Age: 20}

// Found user with `name` = `jinzhu`, attributes will be ignored
db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "jinzhu", Age: 18}

Assign attributes to the record regardless it is found or not and save them back to the database.

// User not found, initialize it with give conditions and Assign attributes
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
// user -> User{ID: 112, Name: "non_existing", Age: 20}

// Found user with `name` = `jinzhu`, update it with Assign attributes
db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// UPDATE users SET age=20 WHERE id = 111;
// user -> User{ID: 111, Name: "jinzhu", Age: 20}

十一、Optimizer/Index Hints

Optimizer hints allow to control the query optimizer to choose a certain query execution plan, GORM supports it with gorm.io/hints, e.g:

import "gorm.io/hints"

db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{})
// SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`

Index hints allow passing index hints to the database in case the query planner gets confused.

import "gorm.io/hints"

db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{})
// SELECT * FROM `users` USE INDEX (`idx_user_name`)

db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{})
// SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"

Refer Optimizer Hints/Index/Comment for more details

十二、Iteration

GORM supports iterating through Rows

rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Rows()
defer rows.Close()

for rows.Next() {
  var user User
  // ScanRows is a method of `gorm.DB`, it can be used to scan a row into a struct
  db.ScanRows(rows, &user)

  // do something
}

FindInBatches

Query and process records in batch

// batch size 100
result := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {
  for _, result := range results {
    // batch processing found records
  }

  tx.Save(&results)

  tx.RowsAffected // number of records in this batch

  batch // Batch 1, 2, 3

  // returns error will stop future batches
  return nil
})

result.Error // returned error
result.RowsAffected // processed records count in all batches

Query Hooks

GORM allows hooks AfterFind for a query, it will be called when querying a record, refer Hooks for details

func (u *User) AfterFind(tx *gorm.DB) (err error) {
  if u.Role == "" {
    u.Role = "user"
  }
  return
}

Pluck

Query single column from database and scan into a slice, if you want to query multiple columns, use Selectwith Scan instead

var ages []int64
db.Model(&users).Pluck("age", &ages)

var names []string
db.Model(&User{}).Pluck("name", &names)

db.Table("deleted_users").Pluck("name", &names)

// Distinct Pluck
db.Model(&User{}).Distinct().Pluck("Name", &names)
// SELECT DISTINCT `name` FROM `users`

// Requesting more than one column, use `Scan` or `Find` like this:
db.Select("name", "age").Scan(&users)
db.Select("name", "age").Find(&users)

Scopes

Scopes allows you to specify commonly-used queries which can be referenced as method calls

func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {
  return db.Where("amount > ?", 1000)
}

func PaidWithCreditCard(db *gorm.DB) *gorm.DB {
  return db.Where("pay_mode_sign = ?", "C")
}

func PaidWithCod(db *gorm.DB) *gorm.DB {
  return db.Where("pay_mode_sign = ?", "C")
}

func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
  return func (db *gorm.DB) *gorm.DB {
    return db.Where("status IN (?)", status)
  }
}

db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)
// Find all credit card orders and amount greater than 1000

db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)
// Find all COD orders and amount greater than 1000

db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders)
// Find all paid, shipped orders that amount greater than 1000

Checkout Scopes for details

Count

Get matched records count

var count int64
db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count)
// SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'

db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
// SELECT count(1) FROM users WHERE name = 'jinzhu'; (count)

db.Table("deleted_users").Count(&count)
// SELECT count(1) FROM deleted_users;

// Count with Distinct
db.Model(&User{}).Distinct("name").Count(&count)
// SELECT COUNT(DISTINCT(`name`)) FROM `users`

db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
// SELECT count(distinct(name)) FROM deleted_users

// Count with Group
users := []User{
  {Name: "name1"},
  {Name: "name2"},
  {Name: "name3"},
  {Name: "name3"},
}

db.Model(&User{}).Group("name").Count(&count)
count // => 3

var count int64
db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count)
// SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'

db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
// SELECT count(1) FROM users WHERE name = 'jinzhu'; (count)

db.Table("deleted_users").Count(&count)
// SELECT count(1) FROM deleted_users;

// Count with Distinct
db.Model(&User{}).Distinct("name").Count(&count)
// SELECT COUNT(DISTINCT(`name`)) FROM `users`

db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
// SELECT count(distinct(name)) FROM deleted_users

// Count with Group
users := []User{
  {Name: "name1"},
  {Name: "name2"},
  {Name: "name3"},
  {Name: "name3"},
}

db.Model(&User{}).Group("name").Count(&count)
count // => 3