GORM查询

1. GORM基本查询

• 官方文档

1.1. 基本查询

func (c *UserController) UserSelect() {
    //1、查询id=3的用户
    user := models.User{Id: 3}
    models.DB.Find(&user)

    //2、查询所有数据
    user := []models.User{}
    result := models.DB.Find(&user)  //5 （统计有几条数据）
    fmt.Println(result.RowsAffected )

    //3、查询第一条数据
    users := models.User{}
    models.DB.First(&users)

    c.Data["json"] = user
    c.ServeJSON()
}

1.2. 用主键检索

func (c *UserController) UserSelect() {
    user := []models.User{}

    //1) SELECT * FROM users WHERE id = 2;
    models.DB.First(&user, 2)
    //2) SELECT * FROM users WHERE id IN (1,2,3);
    models.DB.Find(&user, []int{1,2,3})

    c.Data["json"] = user
    c.ServeJSON()
}

2. 条件查询

2.1. String 条件

func (c *UserController) UserSelect() {
    user := []models.User{}

    //1) Get first matched record
    models.DB.Where("username = ?", "zhangsan").First(&user)
    // SELECT * FROM users WHERE name = 'jinzhu' limit 1;

    //2) Get all matched records
    models.DB.Where("username = ?", "zhangsan").Find(&user)
    // SELECT * FROM users WHERE name = 'jinzhu';

    //3） <>
    models.DB.Where("username <> ?", "jinzhu").Find(&user)
    //SELECT * FROM users WHERE name <> 'jinzhu';

    //4） IN
    models.DB.Where("username IN (?)", []string{"jinzhu", "jinzhu 2"}).Find(&user)
    //5） SELECT * FROM users WHERE name in ('jinzhu','jinzhu 2');

    //6） LIKE
    models.DB.Where("username LIKE ?", "%jin%").Find(&user)
    // SELECT * FROM users WHERE name LIKE '%jin%';

    //7） AND
    models.DB.Where("username = ? AND age >= ?", "jinzhu", "22").Find(&user)
    // SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

    //8） Time
    models.DB.Where("updated_at > ?", lastWeek).Find(&user)
    // SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

    //9） BETWEEN
    models.DB.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&user)
    // SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

    c.Data["json"] = user
    c.ServeJSON()
}

2.2. Struch & Map 查询

func (c *UserController) UserSelect() {
    user := []models.User{}

    // 1）Struct
    models.DB.Where(&models.User{Username: "zhangsan", Age: 26}).First(&user)
    // SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;

    // 2）Map
    models.DB.Where(map[string]interface{}{"username": "zhangsan", "age": 26}).Find(&user)
    // SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

    // 3）主键的切片
    models.DB.Where([]int64{1, 2, 3}).Find(&user)
    //SELECT * FROM users WHERE id IN (20, 21, 22);

    c.Data["json"] = user
    c.ServeJSON()
}

• 定义的结构体

type User struct {
    Id       int
    Username string
    Age      int
    Email    string
    AddTime  int
}

2.3. Not条件

func (c *UserController) UserSelect() {
    user := []models.User{}

    //1）不等于
    models.DB.Not("zhangsan", "lisi").First(&user)
    // SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;

    //2） Not In
    models.DB.Not("username", []string{"zhangsan", "lisi"}).Find(&user)
    //// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

    //3） Not In slice of primary keys
    models.DB.Not([]int64{1,2,3}).First(&user)
    // SELECT * FROM users WHERE id NOT IN (1,2,3);

    //4） Plain SQL
    models.DB.Not("name = ?", "jinzhu").First(&user)
    // SELECT * FROM users WHERE NOT(name = "jinzhu");

    //5） Struct
    models.DB.Not(models.User{Username: "jinzhu"}).First(&user)
    // SELECT * FROM users WHERE name <> "jinzhu";

    c.Data["json"] = user
    c.ServeJSON()
}

2.4. Or条件

func (c *UserController) UserSelect() {
    user := []models.User{}

    //1）
    models.DB.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&user)
    // SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

    //2） Struct
    models.DB.Where("name = 'jinzhu'").Or(models.User{Username: "jinzhu 2"}).Find(&user)
    // SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

    //3） Map
    models.DB.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&user)
    // SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

    c.Data["json"] = user
    c.ServeJSON()
}

3. 高级查询

3.1. 选择特定字段

• 选择您想从数据库中检索的字段，默认情况下会选择全部字段

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;

3.2. Order

• 指定从数据库检索记录时的排序方式

db.Order("age desc, name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

// 多个 order
db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

db.Clauses(clause.OrderBy{
  Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
}).Find(&User{})
// SELECT * FROM users ORDER BY FIELD(id,1,2,3)

3.3. Limit & Offset

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)

3.4. Group & Having

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)

3.5. Distinct

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

4. 连表查询

4.1. 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

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)

4.2. Joins 预加载

• 您可以使用 Joins 实现单条 SQL 预加载关联记录，例如：

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`;