{"path":"1861159","ref":"CRUD接口\/advanced_query.md","title":"高级查询","content":"# 高级查询\n\n## 智能选择字段\n\nGORM 允许通过 [`Select`](http:\/\/v2.gorm.io\/zh_CN\/docs\/query.html) 方法选择特定的字段，如果您在应用程序中经常使用此功能，你可以定义一个较小的 API 结构体，以实现自动选择特定的字段。\n\n```go\ntype User struct {\n  ID     uint\n  Name   string\n  Age    int\n  Gender string\n  \/\/ 假设后面还有几百个字段...\n}\n\ntype APIUser struct {\n  ID   uint\n  Name string\n}\n\n\/\/ 查询时会自动选择 `id`, `name` 字段\ndb.Model(&User{}).Limit(10).Find(&APIUser{})\n\/\/ SELECT `id`, `name` FROM `users` LIMIT 10\n```\n\n## Locking (FOR UPDATE)\n\nGORM 支持多种类型的锁，例如：\n\n```go\nDB.Clauses(clause.Locking{Strength: \"UPDATE\"}).Find(&users)\n\/\/ SELECT * FROM `users` FOR UPDATE\n\nDB.Clauses(clause.Locking{\n  Strength: \"SHARE\",\n  Table: clause.Table{Name: clause.CurrentTable},\n}).Find(&users)\n\/\/ SELECT * FROM `users` FOR SHARE OF `users`\n```\n\n参考 [原生 SQL 及构造器](.\/sql_builder.md) 获取详情\n\n## 子查询\n\n子查询可以嵌套在查询中，GORM 允许在使用 `*gorm.DB` 对象作为参数时生成子查询\n\n```go\ndb.Where(\"amount > ?\", db.Table(\"orders\").Select(\"AVG(amount)\")).Find(&orders)\n\/\/ SELECT * FROM \"orders\" WHERE amount > (SELECT AVG(amount) FROM \"orders\");\n\nsubQuery := db.Select(\"AVG(age)\").Where(\"name LIKE ?\", \"name%\").Table(\"users\")\ndb.Select(\"AVG(age) as avgage\").Group(\"name\").Having(\"AVG(age) > (?)\", subQuery).Find(&results)\n\/\/ SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE \"name%\")\n```\n\n### From 子查询\n\nGORM 允许您在 `Table` 方法中通过 FROM 子句使用子查询，例如：\n\n```go\ndb.Table(\"(?) as u\", DB.Model(&User{}).Select(\"name\", \"age\")).Where(\"age = ?\", 18}).Find(&User{})\n\/\/ SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18\n\nsubQuery1 := DB.Model(&User{}).Select(\"name\")\nsubQuery2 := DB.Model(&Pet{}).Select(\"name\")\ndb.Table(\"(?) as u, (?) as p\", subQuery1, subQuery2).Find(&User{})\n\/\/ SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p\n```\n\n## Group 条件\n\n使用 Group 条件可以更轻松的编写复杂 SQL\n\n```go\ndb.Where(\n    DB.Where(\"pizza = ?\", \"pepperoni\").Where(DB.Where(\"size = ?\", \"small\").Or(\"size = ?\", \"medium\")),\n).Or(\n    DB.Where(\"pizza = ?\", \"hawaiian\").Where(\"size = ?\", \"xlarge\"),\n).Find(&Pizza{}).Statement\n\n\/\/ SELECT * FROM `pizzas` WHERE (pizza = \"pepperoni\" AND (size = \"small\" OR size = \"medium\")) OR (pizza = \"hawaiian\" AND size = \"xlarge\")\n```\n\n## 命名参数\n\nGORM 支持 [`sql.NamedArg`](https:\/\/tip.golang.org\/pkg\/database\/sql\/#NamedArg) 和 `map[string]interface{}{}` 形式的命名参数，例如：\n\n```go\nDB.Where(\"name1 = @name OR name2 = @name\", sql.Named(\"name\", \"jinzhu\")).Find(&user)\n\/\/ SELECT * FROM `users` WHERE name1 = \"jinzhu\" OR name2 = \"jinzhu\"\n\nDB.Where(\"name1 = @name OR name2 = @name\", map[string]interface{}{\"name\": \"jinzhu\"}).First(&user)\n\/\/ SELECT * FROM `users` WHERE name1 = \"jinzhu\" OR name2 = \"jinzhu\" ORDER BY `users`.`id` LIMIT 1\n```\n\n查看 [原生 SQL 及构造器](.\/sql_builder.md#named_argument) 获取详情\n\n## Find 至 map\n\nGORM 允许扫描结果至 `map[string]interface{}` 或 `[]map[string]interface{}`，此时别忘了指定 `Model` 或 `Table`，例如：\n\n```go\nvar result map[string]interface{}\nDB.Model(&User{}).First(&result, \"id = ?\", 1)\n\nvar results []map[string]interface{}\nDB.Table(\"users\").Find(&results)\n```\n\n## FirstOrInit\n\n获取第一条匹配的记录，或者根据给定的条件初始化一个 struct（仅支持 sturct 和 map 条件）\n\n```go\n\/\/ 未找到 user，根据给定的条件初始化 struct\ndb.FirstOrInit(&user, User{Name: \"non_existing\"})\n\/\/ user -> User{Name: \"non_existing\"}\n\n\/\/ 找到了 `name` = `jinzhu` 的 user\ndb.Where(User{Name: \"jinzhu\"}).FirstOrInit(&user)\n\/\/ user -> User{ID: 111, Name: \"Jinzhu\", Age: 18}\n\n\/\/ 找到了 `name` = `jinzhu` 的 user\ndb.FirstOrInit(&user, map[string]interface{}{\"name\": \"jinzhu\"})\n\/\/ user -> User{ID: 111, Name: \"Jinzhu\", Age: 18}\n```\n\n如果没有找到记录，可以使用包含更多的属性的结构体初始化 user，`Attrs` 不会被用于生成查询 SQL\n\n```go\n\/\/ 未找到 user，则根据给定的条件以及 Attrs 初始化 user\ndb.Where(User{Name: \"non_existing\"}).Attrs(User{Age: 20}).FirstOrInit(&user)\n\/\/ SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;\n\/\/ user -> User{Name: \"non_existing\", Age: 20}\n\n\/\/ 未找到 user，则根据给定的条件以及 Attrs 初始化 user\ndb.Where(User{Name: \"non_existing\"}).Attrs(\"age\", 20).FirstOrInit(&user)\n\/\/ SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;\n\/\/ user -> User{Name: \"non_existing\", Age: 20}\n\n\/\/ 找到了 `name` = `jinzhu` 的 user，则忽略 Attrs\ndb.Where(User{Name: \"Jinzhu\"}).Attrs(User{Age: 20}).FirstOrInit(&user)\n\/\/ SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;\n\/\/ user -> User{ID: 111, Name: \"Jinzhu\", Age: 18}\n```\n\n不管是否找到记录，`Assign` 都会将属性赋值给 struct，但这些属性不会被用于生成查询 SQL\n\n```go\n\/\/ 未找到 user，根据条件和 Assign 属性初始化 struct\ndb.Where(User{Name: \"non_existing\"}).Assign(User{Age: 20}).FirstOrInit(&user)\n\/\/ user -> User{Name: \"non_existing\", Age: 20}\n\n\/\/ 找到 `name` = `jinzhu` 的记录，依然会更新 Assign 相关的属性\ndb.Where(User{Name: \"Jinzhu\"}).Assign(User{Age: 20}).FirstOrInit(&user)\n\/\/ SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1;\n\/\/ user -> User{ID: 111, Name: \"Jinzhu\", Age: 20}\n```\n\n## FirstOrCreate\n\n获取第一条匹配的记录，或者根据给定的条件创建一条新纪录（仅支持 sturct 和 map 条件）\n\n```go\n\/\/ 未找到 user，则根据给定条件创建一条新纪录\ndb.FirstOrCreate(&user, User{Name: \"non_existing\"})\n\/\/ INSERT INTO \"users\" (name) VALUES (\"non_existing\");\n\/\/ user -> User{ID: 112, Name: \"non_existing\"}\n\n\/\/ 找到了 `name` = `jinzhu` 的 user\ndb.Where(User{Name: \"jinzhu\"}).FirstOrCreate(&user)\n\/\/ user -> User{ID: 111, Name: \"jinzhu\", \"Age}: 18\n```\n\n如果没有找到记录，可以使用包含更多的属性的结构体创建记录，`Attrs` 不会被用于生成查询 SQL 。\n\n```go\n\/\/ 未找到 user，根据条件和 Assign 属性创建记录\ndb.Where(User{Name: \"non_existing\"}).Attrs(User{Age: 20}).FirstOrCreate(&user)\n\/\/ SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;\n\/\/ INSERT INTO \"users\" (name, age) VALUES (\"non_existing\", 20);\n\/\/ user -> User{ID: 112, Name: \"non_existing\", Age: 20}\n\n\/\/ 找到了 `name` = `jinzhu` 的 user，则忽略 Attrs\ndb.Where(User{Name: \"jinzhu\"}).Attrs(User{Age: 20}).FirstOrCreate(&user)\n\/\/ SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;\n\/\/ user -> User{ID: 111, Name: \"jinzhu\", Age: 18}\n```\n\n不管是否找到记录，`Assign` 都会将属性赋值给 struct，并将结果写回数据库\n\n```go\n\/\/ 未找到 user，根据条件和 Assign 属性创建记录\ndb.Where(User{Name: \"non_existing\"}).Assign(User{Age: 20}).FirstOrCreate(&user)\n\/\/ SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;\n\/\/ INSERT INTO \"users\" (name, age) VALUES (\"non_existing\", 20);\n\/\/ user -> User{ID: 112, Name: \"non_existing\", Age: 20}\n\n\/\/ 找到了 `name` = `jinzhu` 的 user，依然会根据 Assign 更新记录\ndb.Where(User{Name: \"jinzhu\"}).Assign(User{Age: 20}).FirstOrCreate(&user)\n\/\/ SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;\n\/\/ UPDATE users SET age=20 WHERE id = 111;\n\/\/ user -> User{ID: 111, Name: \"jinzhu\", Age: 20}\n```\n\n## 优化器、索引提示\n\n优化器提示允许我们控制查询优化器选择某个查询执行计划。\n\n```go\nimport \"gorm.io\/hints\"\n\nDB.Clauses(hints.New(\"MAX_EXECUTION_TIME(10000)\")).Find(&User{})\n\/\/ SELECT * \/*+ MAX_EXECUTION_TIME(10000) *\/ FROM `users`\n```\n\n索引提示允许传递索引提示到数据库，以防查询计划器出现混乱。\n\n```go\nimport \"gorm.io\/hints\"\n\nDB.Clauses(hints.UseIndex(\"idx_user_name\")).Find(&User{})\n\/\/ SELECT * FROM `users` USE INDEX (`idx_user_name`)\n\nDB.Clauses(hints.ForceIndex(\"idx_user_name\", \"idx_user_id\").ForJoin()).Find(&User{})\n\/\/ SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)\"\n```\n\n参考 [优化器提示、索引、备注](http:\/\/v2.gorm.io\/zh_CN\/docs\/hints.html) 获取详情\n\n## 迭代\n\nGORM 支持通过行进行迭代\n\n```go\nrows, err := db.Model(&User{}).Where(\"name = ?\", \"jinzhu\").Rows()\ndefer rows.Close()\n\nfor rows.Next() {\n  var user User\n  \/\/ ScanRows 将一行记录扫描至 user\n  db.ScanRows(rows, &user)\n\n  \/\/ 业务逻辑...\n}\n```\n\n## FindInBatches\n\n用于批量查询并处理记录\n\n```go\n\/\/ 每次批量处理 100 条\nresult := DB.Where(\"processed = ?\", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int) error {\n  for _, result := range results {\n    \/\/ 批量处理找到的记录\n  }\n\n  tx.Save(&results)\n\n  tx.RowsAffected \/\/ 本次批量操作影响的记录数\n\n  batch \/\/ Batch 1, 2, 3\n\n  \/\/ 如果返回错误会终止后续批量操作\n  return nil\n})\n\nresult.Error \/\/ returned error\nresult.RowsAffected \/\/ 整个批量操作影响的记录数\n```\n\n## 查询钩子\n\n对于查询操作，GORM 支持 `AfterFind` 钩子，查询记录后会调用它，详情请参考 [钩子](http:\/\/v2.gorm.io\/zh_CN\/docs\/hooks.html)\n\n```go\nfunc (u *User) AfterFind(tx *gorm.DB) (err error) {\n  if u.Role == \"\" {\n    u.Role = \"user\"\n  }\n  return\n}\n```\n\n## Pluck\n\nPluck 用于从数据库查询单个列，并将结果扫描到切片。如果您想要查询多列，您应该使用 [`Scan`](http:\/\/v2.gorm.io\/zh_CN\/docs\/advanced_query.html#scan)\n\n```go\nvar ages []int64\ndb.Find(&users).Pluck(\"age\", &ages)\n\nvar names []string\ndb.Model(&User{}).Pluck(\"name\", &names)\n\ndb.Table(\"deleted_users\").Pluck(\"name\", &names)\n\n\/\/ Distinct Pluck\nDB.Model(&User{}).Distinct().Pluck(\"Name\", &names)\n\/\/ SELECT DISTINCT `name` FROM `users`\n\n\/\/ 超过一列的查询，应该使用 `Scan` 或者 `Find`，例如：\ndb.Select(\"name\", \"age\").Scan(&users)\ndb.Select(\"name\", \"age\").Find(&users)\n```\n\n## Scopes\n\n`Scopes` 允许你指定常用的查询，可以在调用方法时引用这些查询\n\n```go\nfunc AmountGreaterThan1000(db *gorm.DB) *gorm.DB {\n  return db.Where(\"amount > ?\", 1000)\n}\n\nfunc PaidWithCreditCard(db *gorm.DB) *gorm.DB {\n  return db.Where(\"pay_mode_sign = ?\", \"C\")\n}\n\nfunc PaidWithCod(db *gorm.DB) *gorm.DB {\n  return db.Where(\"pay_mode_sign = ?\", \"C\")\n}\n\nfunc OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {\n  return func (db *gorm.DB) *gorm.DB {\n    return db.Where(\"status IN (?)\", status)\n  }\n}\n\ndb.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders)\n\/\/ 查找所有金额大于 1000 的信用卡订单\n\ndb.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders)\n\/\/ 查找所有金额大于 1000 的货到付款订单\n\ndb.Scopes(AmountGreaterThan1000, OrderStatus([]string{\"paid\", \"shipped\"})).Find(&orders)\n\/\/ 查找所有金额大于 1000 且已付款或已发货的订单\n```\n\n## Count\n\nCount 用于获取匹配的记录数\n\n```go\nvar count int64\ndb.Model(&User{}).Where(\"name = ?\", \"jinzhu\").Or(\"name = ?\", \"jinzhu 2\").Count(&count)\n\/\/ SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'\n\ndb.Model(&User{}).Where(\"name = ?\", \"jinzhu\").Count(&count)\n\/\/ SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)\n\ndb.Table(\"deleted_users\").Count(&count)\n\/\/ SELECT count(*) FROM deleted_users;\n\n\/\/ 去重计数\nDB.Model(&User{}).Distinct(\"name\").Count(&count)\n\/\/ SELECT COUNT(DISTINCT(`name`)) FROM `users`\n\ndb.Table(\"deleted_users\").Select(\"count(distinct(name))\").Count(&count)\n\/\/ SELECT count(distinct(name)) FROM deleted_users\n\n\/\/ 分组计数\nusers := []User{\n  {Name: \"name1\"},\n  {Name: \"name2\"},\n  {Name: \"name3\"},\n  {Name: \"name3\"},\n}\n\nDB.Model(&User{}).Group(\"name\").Count(&count)\ncount \/\/ => 3\n```\n\n\n"}