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