【GORM】高级查询方案

1.Struct & Map查询为空的情况

当通过结构体进行查询时，GORM将会只通过非零值字段查询，这意味着如果你的字段值为0，‘’，false或者其他零值时，将不会被用于构建查询条件，例如：

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

可以使用指针或实现 Scanner/Valuer 接口来避免这个问题：

// 使用指针
type User struct {gorm.ModelName stringAge  *int
}// 使用 Scanner/Valuer
type User struct {gorm.ModelName stringAge  sql.NullInt64  // sql.NullInt64 实现了 Scanner/Valuer 接口
}

---

2.FirstOrInit

如果记录未找到，将使用参数初始化 struct：

var user User
// 未找到
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
// db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
fmt.Println(user)  // {{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC {0001-01-01 00:00:00 +0000 UTC false}} non_existing 20}

var user User
// 找到
db.Where(User{Name: "xiaoqian"}).Attrs(User{Age: 20}).FirstOrInit(&user)
fmt.Println(user)  // {{2 2023-03-09 21:21:06 +0800 CST 2023-03-09 21:21:06 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} xiaoqian 22}

不管记录是否找到，都将参数赋值给 struct：

// 未找到
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
 user -> User{Name: "non_existing", Age: 20}// 找到
db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user)
 SELECT * FROM USERS WHERE name = jinzhu';
 user -> User{Id: 111, Name: "Jinzhu", Age: 30}

---

3.FirstOrCreate

获取匹配的第一条记录, 否则根据给定的条件创建一个新的记录 (仅支持 struct 和 map 条件)

// 未找到
db.FirstOrCreate(&user, User{Name: "non_existing"})
// INSERT INTO "users" (name) VALUES ("non_existing");

// 找到
db.FirstOrCreate(&user, User{Name: "xiaoqian"})
fmt.Println(user)
// {{2 2023-03-09 21:21:06 +0800 CST 2023-03-09 21:21:06 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} xiaoqian 22}

如果记录未找到，将使用参数创建 struct 和记录：

// 未找到
db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'non_existing';
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);// 找到
db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'jinzhu';

不管记录是否找到，都将参数赋值给 struct 并保存至数据库：

// 未找到
db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'non_existing';
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);// 找到
db.Where(User{Name: "jinzhu"}).Assign(User{Age: 30}).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'jinzhu';
// UPDATE users SET age=30 WHERE id = 111;

---

4.高级查询

子查询：

基于 *gorm.expr 的子查询

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

选择字段：

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

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;

排序：

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, name;

数量：

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

db.Limit(3).Find(&users)
// SELECT * FROM users LIMIT 3;

偏移：

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

db.Offset(3).Find(&users)

总数：

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

db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)
// SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users)
// SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)db.Table("deleted_users").Count(&count)
// SELECT count(*) FROM deleted_users;db.Table("deleted_users").Select("count(distinct(name))").Count(&count)
// SELECT count( distinct(name) ) FROM deleted_users; (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() {...
}// 使用Scan将多条结果扫描进事先准备好的结构体切片中
type Result struct {Date time.TimeTotal 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() {...
}type Result struct {Date  time.TimeTotal int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

连接：

Joins，指定连接条件

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 = ?", "[e-mail]").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)

扫描：

Scan，扫描结果至一个 struct

type Result struct {Name stringAge  int
}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)