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)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211208084736666.png$

注意 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`

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211208085147537.png$

二、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)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211208085613429.png$

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)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211208090838067.png$

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211208090847456.png$

四、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)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211208220632303.png$

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)
}

$E:\Lenrn_notes\21go\2.0笔记整理\image\image-20211208221755889.png$

五、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

posted @ 2021-12-08 22:37 RandySun 阅读(2944) 评论(0) 编辑收藏举报

刷新页面返回顶部

RandySun

有角度，才会有方向在当下的阶段，必将由程序员来主导，甚至比以往更甚

GORM高级查询

GORM高级查询

一、智能选择字段

二、Locking (FOR UPDATE)

三、子查询

四、From 子查询

五、Group 条件

六、IN with multiple columns

七、Named Argument

八、Find To Map

九、FirstOrInit

十、FirstOrCreate

十一、Optimizer/Index Hints

十二、Iteration

FindInBatches

Query Hooks

Pluck

Scopes

Count

公告

RandySun

有角度，才会有方向 在当下的阶段，必将由程序员来主导，甚至比以往更甚

GORM高级查询

GORM高级查询

一、智能选择字段

二、Locking (FOR UPDATE)

三、子查询

四、From 子查询

五、Group 条件

六、IN with multiple columns

七、Named Argument

八、Find To Map

九、FirstOrInit

十、FirstOrCreate

十一、Optimizer/Index Hints

十二、Iteration

FindInBatches

Query Hooks

Pluck

Scopes

Count

公告

有角度，才会有方向在当下的阶段，必将由程序员来主导，甚至比以往更甚