GORM—查-阿里云开发者社区

简单查询

查询单条

  var user User
  // 根据主键查询第一条记录SELECT * FROM users ORDER BY id LIMIT 1;
  db.First(&user)
  // 随机获取一条记录SELECT * FROM users LIMIT 1;
  db.Take(&user)
  // 根据主键查询最后一条记录SELECT * FROM users ORDER BY id DESC LIMIT 1;
  db.Last(&user)
  // 查询指定的某条记录(仅当主键为整型时可用)SELECT * FROM users WHERE id = 10;
  db.First(&user, 10)

查询多条

  users := make([]User, 0)
  // 查询所有的记录SELECT * FROM users;
  db.Find(&users)

Where条件

  var user User
  users := make([]User, 0)
  // Get first matched record
  //SELECT * FROM users WHERE name = 'wxf' limit 1;
  db.Where("name = ?", "wxf").First(&user)
  // Get all matched records
  //SELECT * FROM users WHERE name = 'wxf';
  db.Where("name = ?", "wxf").Find(&users)
  // <>
  //SELECT * FROM users WHERE name <> 'wxf';
  db.Where("name <> ?", "wxf").Find(&users)
  // IN
  //SELECT * FROM users WHERE name in ('wxf','wxf 2');
  db.Where("name IN (?)", []string{"wxf", "wxf 2"}).Find(&users)
  // LIKE
  //SELECT * FROM users WHERE name LIKE '%w%';
  db.Where("name LIKE ?", "%w%").Find(&users)
  // AND
  //SELECT * FROM users WHERE name = 'wxf' AND age >= 20;
  db.Where("name = ? AND age >= ?", "wxf", "20").Find(&users)
  // Time
  //SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';
  db.Where("updated_at > ?", lastWeek).Find(&users)
  // BETWEEN
  //SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';
  db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

  // Struct
  //SELECT * FROM users WHERE name = "wxf" AND age = 20 LIMIT 1;
  db.Where(&User{Name: "wxf", Age: 20}).First(&user)
  // Map
  //SELECT * FROM users WHERE name = "wxf" AND age = 20;
  db.Where(map[string]interface{}{"name": "wxf", "age": 20}).Find(&users)
  // 主键的切片
  //SELECT * FROM users WHERE id IN (20, 21, 22);
  db.Where([]int64{20, 21, 22}).Find(&users)

当通过结构体查询的时候，GORM只会查询结构体中非零值的字段，

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

那如果就想查询零值(0,“”,false)怎么办呢，以使用指针或实现 Scanner/Valuer 接口来解决这个问题

// 使用指针
type User struct {
  gorm.Model
  Name string
  Age  *int
}
// 使用 Scanner/Valuer
type User struct {
  gorm.Model
  Name string
  Age  sql.NullInt64  // sql.NullInt64 实现了 Scanner/Valuer 接口
}

Not

  //SELECT * FROM users WHERE name <> "wxf" LIMIT 1;
  db.Not("name", "wxf").First(&user)
  // Not In
  //SELECT * FROM users WHERE name NOT IN ("wxf", "wxf 2");
  db.Not("name", []string{"wxf", "wxf 2"}).Find(&users)
  // Not In slice of primary keys
  //SELECT * FROM users WHERE id NOT IN (1,2,3);
  db.Not([]int64{1,2,3}).First(&user)
  //SELECT * FROM users;
  db.Not([]int64{}).First(&user)
  // Plain SQL
  //SELECT * FROM users WHERE NOT(name = "wxf");
  db.Not("name = ?", "wxf").First(&user)
  // Struct
  //SELECT * FROM users WHERE name <> "wxf";
  db.Not(User{Name: "wxf"}).First(&user)
}

Or

  // SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';
  db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
  // Struct
  // SELECT * FROM users WHERE name = 'wxf' OR name = 'wxf 2';
  db.Where("name = 'wxf'").Or(User{Name: "wxf 2"}).Find(&users)
  // Map
  // SELECT * FROM users WHERE name = 'wxf' OR name = 'wxf 2';
  db.Where("name = 'wxf'").Or(map[string]interface{}{"name": "wxf 2"}).Find(&users)

内联查询

Immediate methods ，立即执行方法：是指那些会立即生成SQL语句并发送到数据库的方法, 他们一般是CRUD方法，比如：

Create, First, Find, Take, Save, UpdateXXX, Delete, Scan, Row, Rows

内联条件：作用与Where查询类似

当内联条件与多个立即执行方法一起使用时, 内联条件不会传递给后面的立即执行方法。

  // 根据主键获取记录 (只适用于整形主键)
  // SELECT * FROM users WHERE id = 23 LIMIT 1;
  db.First(&user, 23)
  // Struct
  // SELECT * FROM users WHERE age = 20;
  db.Find(&users, User{Age: 20})
  // Map
  // SELECT * FROM users WHERE age = 20;
  db.Find(&users, map[string]interface{}{"age": 20})
  // 根据主键获取记录, 如果它是一个非整形主键
  // SELECT * FROM users WHERE id = 'string_primary_key' LIMIT 1;
  db.First(&user, "id = ?", "string_primary_key")
  // Plain SQL
  // SELECT * FROM users WHERE name = "wxf";
  db.Find(&user, "name = ?", "wxf")
  // SELECT * FROM users WHERE name <> "wxf" AND age > 20;
  db.Find(&users, "name <> ? AND age > ?", "wxf", 20)

额外查询选项

// 为查询 SQL 添加额外的 SQL 操作
// SELECT * FROM users WHERE id = 10 FOR UPDATE;
db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10)

子查询

  // SELECT * FROM "orders"  WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders"  WHERE (state = 'paid')));
  db.Where("amount > ?", db.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").SubQuery()).Find(&orders)

Select选择字段

Select，指定你想从数据库中检索出的字段，默认会选择全部字段。

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

Order排序

Order，指定从数据库中检索出记录的顺序。设置第二个参数 reorder 为 true ，可以覆盖前面定义的排序条件。

  // SELECT * FROM users ORDER BY age desc, name;
  db.Order("age desc, name").Find(&users)
  // 多字段排序
  // SELECT * FROM users ORDER BY age desc, name;
  db.Order("age desc").Order("name").Find(&users)
  // 覆盖排序
  // SELECT * FROM users ORDER BY age desc; (users1)
  // SELECT * FROM users ORDER BY age; (users2)
  db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)

Limit数量

指定从数据库检索出的最大记录数。

  // SELECT * FROM users LIMIT 3;
  db.Limit(3).Find(&users)
  // -1 取消 Limit 条件
  // SELECT * FROM users LIMIT 10; (users1)
  // SELECT * FROM users; (users2)
  db.Limit(10).Find(&users1).Limit(-1).Find(&users2)

Offset

Offset，指定开始返回记录前要跳过的记录数。

  // SELECT * FROM users OFFSET 3;
  db.Offset(3).Find(&users)
  // -1 取消 Offset 条件
  // SELECT * FROM users OFFSET 10; (users1)
  // SELECT * FROM users; (users2)
  db.Offset(10).Find(&users1).Offset(-1).Find(&users2)

Count

Count，该 model 能获取的记录总数。

  // SELECT * from USERS WHERE name = 'wxf' OR name = 'wxf 2'; (users)
  // SELECT count(*) FROM users WHERE name = 'wxf' OR name = 'wxf 2'; (count)
  db.Where("name = ?", "wxf").Or("name = ?", "wxf 2").Find(&users).Count(&count)
  // SELECT count(*) FROM users WHERE name = 'wxf'; (count)
  db.Model(&User{}).Where("name = ?", "wxf").Count(&count)
  // SELECT count(*) FROM deleted_users;
  db.Table("deleted_users").Count(&count)
  // SELECT count( distinct(name) ) FROM deleted_users; (count)
  db.Table("deleted_users").Select("count(distinct(name))").Count(&count)

注意 Count 必须是链式查询的最后一个操作，因为它会覆盖前面的 SELECT，但如果里面使用了 count 时不会覆盖

Group & Having

  rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
  for rows.Next() {
    rows.Scan()
    ...
  }
  // 使用Scan将多条结果扫描进事先准备好的结构体切片中
  type Result struct {
    Date time.Time
    Total int
  }
  var rets []Result
  db.Table("users").Select("date(created_at) as date, sum(age) as total").Group("date(created_at)").Scan(&rets)
  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() {
    ...
  }

Joins连接

Joins，指定连接条件

  //select users.name, emails.email form user 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() {
    ...
  }
  //select users.name, emails.email form user left join emails on emails.user_id = users.id
  results := make([]User, 0)
  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)

Pluck

Pluck，查询 model 中的一个列作为切片，如果您想要查询多个列，您应该使用 Scan

var ages []int64
db.Find(&users).Pluck("age", &ages)
var names []string
db.Model(&User{}).Pluck("name", &names)
db.Table("deleted_users").Pluck("name", &names)
// 想查询多个字段？ 这样做：
db.Select("name, age").Find(&users)

Scan扫描

Scan，扫描结果至一个 struct

  var result Result
  db.Table("users").Select("name, age").Where("name = ?", "Antonio").Scan(&result)
  var results []Result
  db.Table("users").Select("name, age").Where("id > ?", 0).Scan(&results)
  // 原生 SQL
  db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)

GORM—查