【GORM】高级查询方案
- 1.Struct & Map查询为空的情况
- 2.FirstOrInit
- 3.FirstOrCreate
- 4.高级查询
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 = ?", "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)
扫描：
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)
