检索单个对象

GORM 提供了 First、Take、Last 方法，以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1 条件，且没有找到记录时，它会返回 ErrRecordNotFound 错误

  1. // 获取第一条记录（主键升序）
  2. db.First(&user)
  3. // SELECT * FROM users ORDER BY id LIMIT 1;
  5. // 获取一条记录，没有指定排序字段
  6. db.Take(&user)
  7. // SELECT * FROM users LIMIT 1;
  9. // 获取最后一条记录（主键降序）
  10. db.Last(&user)
  11. // SELECT * FROM users ORDER BY id DESC LIMIT 1;
  13. result := db.First(&user)
  14. result.RowsAffected // 返回找到的记录数
  15. result.Error        // returns error or nil
  17. // 检查 ErrRecordNotFound 错误
  18. errors.Is(result.Error, gorm.ErrRecordNotFound)

如果你想避免ErrRecordNotFound错误，你可以使用Find，比如db.Limit(1).Find(&user)，Find方法可以接受struct和slice的数据。

First 和 Last 会根据主键排序，分别查询第一条和最后一条记录。 只有在目标 struct 是指针或者通过 db.Model() 指定 model 时，该方法才有效。 此外，如果相关 model 没有定义主键，那么将按 model 的第一个字段进行排序。 例如:

  1. var user User
  2. var users []User
  4. // works because destination struct is passed in
  5. db.First(&user)
  6. // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
  8. // works because model is specified using `db.Model()`
  9. result := map[string]interface{}{}
  10. db.Model(&User{}).First(&result)
  11. // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
  13. // doesn't work
  14. result := map[string]interface{}{}
  15. db.Table("users").First(&result)
  17. // works with Take
  18. result := map[string]interface{}{}
  19. db.Table("users").Take(&result)
  21. // no primary key defined, results will be ordered by first field (i.e., `Code`)
  22. type Language struct {
  23.   Code string
  24.   Name string
  25. }
  26. db.First(&Language{})
  27. // SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1

用主键检索

如果主键是数字类型，您可以使用 内联条件 来检索对象。 传入字符串参数时，需要特别注意 SQL 注入问题，查看 安全 获取详情.

  1. db.First(&user, 10)
  2. // SELECT * FROM users WHERE id = 10;
  4. db.First(&user, "10")
  5. // SELECT * FROM users WHERE id = 10;
  7. db.Find(&users, []int{1,2,3})
  8. // SELECT * FROM users WHERE id IN (1,2,3);

如果主键是字符串（例如像 uuid），查询将被写成这样：

  1. db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")
  2. // SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";

When the destination object has a primary value, the primary key will be used to build the condition, for example:

  1. var user = User{ID: 10}
  2. db.First(&user)
  3. // SELECT * FROM users WHERE id = 10;
  5. var result User
  6. db.Model(User{ID: 10}).First(&result)
  7. // SELECT * FROM users WHERE id = 10;

检索全部对象

  1. // Get all records
  2. result := db.Find(&users)
  3. // SELECT * FROM users;
  5. result.RowsAffected // returns found records count, equals `len(users)`
  6. result.Error        // returns error

条件

String 条件

  1. // Get first matched record
  2. db.Where("name = ?", "jinzhu").First(&user)
  3. // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
  5. // Get all matched records
  6. db.Where("name <> ?", "jinzhu").Find(&users)
  7. // SELECT * FROM users WHERE name <> 'jinzhu';
  9. // IN
  10. db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
  11. // SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');
  13. // LIKE
  14. db.Where("name LIKE ?", "%jin%").Find(&users)
  15. // SELECT * FROM users WHERE name LIKE '%jin%';
  17. // AND
  18. db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
  19. // SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;
  21. // Time
  22. db.Where("updated_at > ?", lastWeek).Find(&users)
  23. // SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';
  25. // BETWEEN
  26. db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
  27. // SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

Struct & Map 条件

  1. // Struct
  2. db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
  3. // SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;
  5. // Map
  6. db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
  7. // SELECT * FROM users WHERE name = "jinzhu" AND age = 20;
  9. // Slice of primary keys
  10. db.Where([]int64{20, 21, 22}).Find(&users)
  11. // SELECT * FROM users WHERE id IN (20, 21, 22);

NOTE When querying with struct, GORM will only query with non-zero fields, that means if your field’s value is 0, '', false or other zero values, it won’t be used to build query conditions, for example:

  1. db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
  2. // SELECT * FROM users WHERE name = "jinzhu";

To include zero values in the query conditions, you can use a map, which will include all key-values as query conditions, for example:

  1. db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users)
  2. // SELECT * FROM users WHERE name = "jinzhu" AND age = 0;

For more details, see Specify Struct search fields.

指定结构体查询字段

When searching with struct, you can specify which particular values from the struct to use in the query conditions by passing in the relevant field name or the dbname to Where(), for example:

  1. db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users)
  2. // SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
  4. db.Where(&User{Name: "jinzhu"}, "Age").Find(&users)
  5. // SELECT * FROM users WHERE age = 0;

内联条件

Query conditions can be inlined into methods like First and Find in a similar way to Where.

  1. // Get by primary key if it were a non-integer type
  2. db.First(&user, "id = ?", "string_primary_key")
  3. // SELECT * FROM users WHERE id = 'string_primary_key';
  5. // Plain SQL
  6. db.Find(&user, "name = ?", "jinzhu")
  7. // SELECT * FROM users WHERE name = "jinzhu";
  9. db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
  10. // SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;
  12. // Struct
  13. db.Find(&users, User{Age: 20})
  14. // SELECT * FROM users WHERE age = 20;
  16. // Map
  17. db.Find(&users, map[string]interface{}{"age": 20})
  18. // SELECT * FROM users WHERE age = 20;

Not 条件

Build NOT conditions, works similar to Where

  1. db.Not("name = ?", "jinzhu").First(&user)
  2. // SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;
  4. // Not In
  5. db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)
  6. // SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");
  8. // Struct
  9. db.Not(User{Name: "jinzhu", Age: 18}).First(&user)
  10. // SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;
  12. // Not In slice of primary keys
  13. db.Not([]int64{1,2,3}).First(&user)
  14. // SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;

Or 条件

  1. db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
  2. // SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';
  4. // Struct
  5. db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)
  6. // SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);
  8. // Map
  9. db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)
  10. // SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);

For more complicated SQL queries. please also refer to Group Conditions in Advanced Query.

选择特定字段

Select allows you to specify the fields that you want to retrieve from database. Otherwise, GORM will select all fields by default.

  1. db.Select("name", "age").Find(&users)
  2. // SELECT name, age FROM users;
  4. db.Select([]string{"name", "age"}).Find(&users)
  5. // SELECT name, age FROM users;
  7. db.Table("users").Select("COALESCE(age,?)", 42).Rows()
  8. // SELECT COALESCE(age,'42') FROM users;

Also check out Smart Select Fields

Order

Specify order when retrieving records from the database

  1. db.Order("age desc, name").Find(&users)
  2. // SELECT * FROM users ORDER BY age desc, name;
  4. // Multiple orders
  5. db.Order("age desc").Order("name").Find(&users)
  6. // SELECT * FROM users ORDER BY age desc, name;
  8. db.Clauses(clause.OrderBy{
  9.   Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
  10. }).Find(&User{})
  11. // SELECT * FROM users ORDER BY FIELD(id,1,2,3)

Limit & Offset

Limit specify the max number of records to retrieve Offset specify the number of records to skip before starting to return the records

  1. db.Limit(3).Find(&users)
  2. // SELECT * FROM users LIMIT 3;
  4. // Cancel limit condition with -1
  5. db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
  6. // SELECT * FROM users LIMIT 10; (users1)
  7. // SELECT * FROM users; (users2)
  9. db.Offset(3).Find(&users)
  10. // SELECT * FROM users OFFSET 3;
  12. db.Limit(10).Offset(5).Find(&users)
  13. // SELECT * FROM users OFFSET 5 LIMIT 10;
  15. // Cancel offset condition with -1
  16. db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
  17. // SELECT * FROM users OFFSET 10; (users1)
  18. // SELECT * FROM users; (users2)

Refer to Pagination for details on how to make a paginator

Group By & Having

  1. type result struct {
  2.   Date  time.Time
  3.   Total int
  4. }
  6. db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)
  7. // SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1
  10. db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)
  11. // SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"
  13. rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
  14. defer rows.Close()
  15. for rows.Next() {
  16.   ...
  17. }
  19. rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
  20. defer rows.Close()
  21. for rows.Next() {
  22.   ...
  23. }
  25. type Result struct {
  26.   Date  time.Time
  27.   Total int64
  28. }
  29. db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

Distinct

Selecting distinct values from the model

  1. db.Distinct("name", "age").Order("name, age desc").Find(&results)

Distinct works with Pluck and Count too

Joins

Specify Joins conditions

  1. type result struct {
  2.   Name  string
  3.   Email string
  4. }
  6. db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})
  7. // SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id
  9. rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
  10. for rows.Next() {
  11.   ...
  12. }
  14. db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)
  16. // multiple joins with parameter
  17. 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 预加载

You can use Joins eager loading associations with a single SQL, for example:

  1. db.Joins("Company").Find(&users)
  2. // 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`;

Join with conditions

  1. db.Joins("Company", DB.Where(&Company{Alive: true})).Find(&users)
  2. // 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` AND `Company`.`alive` = true;

For more details, please refer to Preloading (Eager Loading).

Joins a Derived Table

You can also use Joins to join a derived table.

  1. type User struct {
  2.     Id  int
  3.     Age int
  4. }
  6. type Order struct {
  7.     UserId     int
  8.     FinishedAt *time.Time
  9. }
  11. query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id")
  12. db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results)
  13. // SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latest

Scan

Scanning results into a struct works similarly to the way we use Find

  1. type Result struct {
  2.   Name string
  3.   Age  int
  4. }
  6. var result Result
  7. db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)
  9. // Raw SQL
  10. db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)