查詢 · GORM V2 中文文檔

# 查詢 ## 檢索單個對象 GORM 提供 `First`, `Take`, `Last` 方法，以便從數據庫中檢索單個對象。當查詢數據庫時它添加了 `LIMIT 1` 條件。當沒有找到記錄時，它會返回錯誤 `ErrRecordNotFound` ```go // 獲取第一條記錄（主鍵升序） db.First(&user) // SELECT * FROM users ORDER BY id LIMIT 1; // 獲取一條記錄，沒有指定排序字段 db.Take(&user) // SELECT * FROM users LIMIT 1; // 獲取最后一條記錄（主鍵降序） db.Last(&user) // SELECT * FROM users ORDER BY id DESC LIMIT 1; result := db.First(&user) result.RowsAffected // 返回找到的記錄數 result.Error // returns error // 檢查 ErrRecordNotFound 錯誤 errors.Is(result.Error, gorm.ErrRecordNotFound) ``` ## 檢索對象 ```go // 獲取全部記錄 result := db.Find(&users) // SELECT * FROM users; result.RowsAffected // 返回找到的記錄數，相當于 `len(users)` result.Error // returns error ``` ## 條件 ### String 條件 ```go // 獲取第一條匹配的記錄 db.Where("name = ?", "jinzhu").First(&user) // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1; // 獲取全部匹配的記錄 db.Where("name <> ?", "jinzhu").Find(&users) // SELECT * FROM users WHERE name <> 'jinzhu'; // IN db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users) // SELECT * FROM users WHERE name in ('jinzhu','jinzhu 2'); // LIKE db.Where("name LIKE ?", "%jin%").Find(&users) // SELECT * FROM users WHERE name LIKE '%jin%'; // AND db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users) // SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22; // Time db.Where("updated_at > ?", lastWeek).Find(&users) // SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00'; // BETWEEN db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users) // SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00'; ``` ### Struct & Map 條件 ```go // Struct db.Where(&User{Name: "jinzhu", Age: 20}).First(&user) // SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1; // Map db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 20; // 主鍵切片條件 db.Where([]int64{20, 21, 22}).Find(&users) // SELECT * FROM users WHERE id IN (20, 21, 22); ``` **注意** 當使用結構作為條件查詢時，GORM 只會查詢非零值字段。這意味著如果您的字段值為 `0`、`''`、`false` 或其他 [零值](https://tour.golang.org/basics/12)，該字段不會被用于構建查詢條件，例如： ```go db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu"; ``` 您可以使用 map 來構建查詢條件，例如： ```go db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 0; ``` ### 內聯條件用法與 `Where` 類似 ```go // 根據主鍵獲取記錄（僅適用于整型主鍵） db.First(&user, 23) // SELECT * FROM users WHERE id = 23; // 根據主鍵獲取記錄，如果是非整型主鍵 db.First(&user, "id = ?", "string_primary_key") // SELECT * FROM users WHERE id = 'string_primary_key'; // Plain SQL db.Find(&user, "name = ?", "jinzhu") // SELECT * FROM users WHERE name = "jinzhu"; db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20) // SELECT * FROM users WHERE name <> "jinzhu" AND age > 20; // Struct db.Find(&users, User{Age: 20}) // SELECT * FROM users WHERE age = 20; // Map db.Find(&users, map[string]interface{}{"age": 20}) // SELECT * FROM users WHERE age = 20; ``` ### Not 條件構建 NOT 條件，用法與 `Where` 類似 ```go db.Not("name = ?", "jinzhu").First(&user) // SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1; // Not In db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users) // SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2"); // Struct db.Not(User{Name: "jinzhu", Age: 18}).First(&user) // SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1; // 不在主鍵切片中的記錄 db.Not([]int64{1,2,3}).First(&user) // SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1; ``` ### Or 條件 ```go db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users) // SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin'; // Struct db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users) // SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18); // Map db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users) // SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; ``` 您還可以在高級查詢中查看 [Group 條件](./advanced_query.html)，更輕松地編寫復雜 SQL ## 選擇特定字段選擇您想從數據庫中檢索的字段，默認情況下會選擇全部字段 ```go db.Select("name", "age").Find(&users) // SELECT name, age FROM users; db.Select([]string{"name", "age"}).Find(&users) // SELECT name, age FROM users; db.Table("users").Select("COALESCE(age,?)", 42).Rows() // SELECT COALESCE(age,'42') FROM users; ``` ## Order 指定從數據庫檢索記錄時的排序方式 ``` db.Order("age desc, name").Find(&users) // SELECT * FROM users ORDER BY age desc, name; // Multiple orders db.Order("age desc").Order("name").Find(&users) // SELECT * FROM users ORDER BY age desc, name; ``` ## Limit & Offset `Limit` 指定獲取記錄的最大數量 `Offset` 指定在開始返回記錄之前要跳過的記錄數量 ```go db.Limit(3).Find(&users) // SELECT * FROM users LIMIT 3; // 通過 -1 消除 Limit 條件 db.Limit(10).Find(&users1).Limit(-1).Find(&users2) // SELECT * FROM users LIMIT 10; (users1) // SELECT * FROM users; (users2) db.Offset(3).Find(&users) // SELECT * FROM users OFFSET 3; db.Limit(10).Offset(5).Find(&users) // SELECT * FROM users OFFSET 5 LIMIT 10; // 通過 -1 消除 Offset 條件 db.Offset(10).Find(&users1).Offset(-1).Find(&users2) // SELECT * FROM users OFFSET 10; (users1) // SELECT * FROM users; (users2) ``` ## Group & Having ```go type result struct { Date time.Time Total int } db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result) // SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result) // SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group" rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows() for rows.Next() { ... } rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows() for rows.Next() { ... } type Result struct { Date time.Time Total int64 } db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results) ``` ## Distinct 從模型中選擇不相同的值 ```go db.Distinct("name", "age").Order("name, age desc").Find(&results) ``` `Distinct` 也可以配合 [`Pluck`](http://v2.gorm.io/zh_CN/docs/advanced_query.html#pluck), [`Count`](http://v2.gorm.io/zh_CN/docs/advanced_query.html#count) 使用 ## Joins 指定 Joins 條件 ```go 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 rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows() for rows.Next() { ... } db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results) // 帶參數的多表連接 db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user) ``` ### Joins 預加載您可以使用 `Joins` 實現單條 SQL 預加載關聯記錄，例如： ```go db.Joins("Company").Find(&users) // SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`; ``` 參考 [預加載](./preload.html) 了解詳情 ## Scan Scan 結果至 struct，用法與 `Find` 類似 ```go 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) ```