Where查询

直接写条件

go
var user UserModel
DB.Where("id = 5").Take(&user)
fmt.Println(user)


user = UserModel{}
DB.Where("id = 5 or age = 11").Take(&user)
fmt.Println(user)
global.DB.Debug().Where("age>?", "19").Take(&user)
fmt.Println(user)

注意where的顺序，再&函数的前面

结构体条件

非空字段查询

go
var user = UserModel{ID: 5, Name: "李四"}
DB.Debug().Where(user).Take(&user)
// SELECT * FROM `user_models` WHERE (`user_models`.`id` = 5 AND `user_models`.`name` = '李四') AND `user_models`.`id` = 5IMIT 1
fmt.Println(user)

take、first、last只会查主键

Map 条件

go
var user UserModel
DB.Debug().Where(map[string]any{
  "age": 0,
}).Take(&user)
// SELECT * FROM `user_models` WHERE `age` = 0  LIMIT 1
fmt.Println(user)

嵌套where条件

go
query := DB.Where("age = ? and name = ?", 11, "王五1")


var user UserModel
DB.Debug().Where(query).Take(&user)
// SELECT * FROM `user_models` WHERE (age = 11 and name = '王五1') LIMIT 1
fmt.Println(user)

or 或查询

go
var user UserModel
DB.Debug().Or("name = ?", "王五").Or("age = 12").Take(&user)
// SELECT * FROM `user_models` WHERE (name = '王五' OR age = 12) LIMIT 1
fmt.Println(user)

Not 非查询

go
var user UserModel
DB.Debug().Not("age = 12").Take(&user)
//  SELECT * FROM `user_models` WHERE NOT age = 12 LIMIT 1
fmt.Println(user)

排序

go
var userList []UserModel
// 降序
DB.Order("age desc").Find(&userList)
fmt.Println(userList)
// 升序
DB.Order("age asc").Find(&userList)
fmt.Println(userList)

scan

只需要特定字段

go
  var nameList []string
  //删除更新的时候要传指针，查询的时候可以不传
  global.DB.Model(models.UserModel{}).Select("usernamme").Scan(&nameList)
  fmt.Println(nameList)

或者可以直接使用Pluck

go
  global.DB.Model(models.UserModel{}).Pluck("usernamme", &nameList)
  fmt.Println(nameList)

如果结果只要指定的列，可以新建一个结构体

go
  type User struct {
    usernamme string
    age       int
  }
  var userList []User
  global.DB.Model(models.UserModel{}).Scan(&userList)
  fmt.Println(userList)

如果对应不上

go
  type User struct {
    Lable string `gorm:"column:usernamme"`
    Value int    `gorm:"column:age"`
  }
  var userList []User
  global.DB.Model(models.UserModel{}).Scan(&userList)
  fmt.Println(userList)

如果要分组

go
  type userCount struct {
    Age   int
    Count int
  }
  var list []userCount
  global.DB.Model(models.UserModel{}).Group("age").Select("age", "count(id) as count").Scan(&list)
  fmt.Println(list)

去重

go
  var ageList []int
  global.DB.Model(models.UserModel{}).Distinct("age").Pluck("age", &ageList)
  fmt.Println(ageList)

分页查询

sql
  var userList []models.UserModel
  limit := 3
  page := 1
  //global.DB.Limit(2).Offset(0).Find(&userList)
  //fmt.Println(userList)
  //global.DB.Limit(2).Offset(3).Find(&userList)
  //fmt.Println(userList)
  global.DB.Limit(limit).Offset((page - 1) * limit).Find(&userList)
  fmt.Println(userList)

Scope

很多时候，我们会经常使用重复的查询方法

那么就可以把这些可能被复用的方法抽离出来

go
func Age18(tx *gorm.DB) *gorm.DB {
  return tx.Where("age >= ?", 18)
}


var users []UserModel
DB.Scopes(Age18).Find(&users)
fmt.Println(users)

还可以使用高阶函数的特点，实现带参数的scope

go
func NameIn(nameList ...string) func(tx *gorm.DB) *gorm.DB {
  return func(tx *gorm.DB) *gorm.DB {
    return tx.Where("name in  ?", nameList)
  }
}


var users []UserModel
DB.Scopes(NameIn("王五", "李四")).Find(&users)
fmt.Println(users)

查询钩子

go
func (u *UserModel) AfterFind(tx *gorm.DB) (err error) {
  fmt.Println("查询钩子")
  return
}

原生SQL

go
var users []UserModel
DB.Raw("select * from user_models where name = ?", "王五").Scan(&users)
fmt.Println(users)

使用Exec来操作数据

sql
DB.Exec("update user_models set name = ? where id = ?", "张三", 4)