网站建设 选猴王网络,如何做游戏推广,文件管理系统 wordpress,中国营销传播网手机版【GORM】高级查询方案1.Struct Map查询为空的情况2.FirstOrInit3.FirstOrCreate4.高级查询1.Struct Map查询为空的情况
当通过结构体进行查询时#xff0c;GORM将会只通过非零值字段查询#xff0c;这意味着如果你的字段值为0#xff0c;‘’#xff0c;false…
【GORM】高级查询方案1.Struct Map查询为空的情况2.FirstOrInit3.FirstOrCreate4.高级查询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 age30 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 ?, jinzhuexample.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)
