Skip to main content

高级查询

智能选择字段​

GORM 允许选择特定的字段：

type APIUser struct {
  ID   uint
  Name string
}

// 查询时会自动选择 APIUser 中定义的字段
db.Model(&User{}).Limit(10).Find(&APIUser{})
// SELECT `id`, `name` FROM `users` LIMIT 10

锁 (Locking)​

GORM 支持不同类型的锁，例如 FOR UPDATE。

db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10)
// SELECT * FROM users WHERE id = 10 FOR UPDATE;

或者使用 Clauses:

import "gorm.io/gorm/clause"

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

子查询 (SubQuery)​

db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)
// SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");

subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users")
db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results)
// SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")

Group & Having​

db.Model(&User{}).Select("name, sum(age) as total").Group("name").Find(&result)

db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)

Distinct​

db.Distinct("name", "age").Order("name, age desc").Find(&results)
// SELECT DISTINCT name, age FROM users ORDER BY name, age desc

Joins​

type Result struct {
  Name  string
  Email string
}
db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result)
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id

// Joins 预加载
db.Joins("Company").Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`company_id`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`

Scan​

Scan 用于将结果扫描到结构体中，类似于 Find。

type Result struct {
  Name string
  Age  int
}

var result Result
db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)

// 原生 SQL
db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)