db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders) // SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");
subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users") db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results) // SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")
From 子查询
GORM 允许您在 Table 方法中通过 FROM 子句使用子查询,例如:
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).Where("age = ?", 18).Find(&User{}) // SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18
subQuery1 := db.Model(&User{}).Select("name") subQuery2 := db.Model(&Pet{}).Select("name") db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{}) // SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user) // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu"}).First(&user) // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu" ORDER BY `users`.`id` LIMIT 1
// 未找到 user,则根据给定的条件以及 Attrs 初始化 user db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1; // user -> User{Name: "non_existing", Age: 20}
// 未找到 user,则根据给定的条件以及 Attrs 初始化 user db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1; // user -> User{Name: "non_existing", Age: 20}
// 找到了 `name` = `jinzhu` 的 user,则忽略 Attrs db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 20}).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1; // user -> User{ID: 111, Name: "Jinzhu", Age: 18}
// 找到 `name` = `jinzhu` 的记录,依然会更新 Assign 相关的属性 db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 20}).FirstOrInit(&user) // SELECT * FROM USERS WHERE name = jinzhu' ORDER BY id LIMIT 1; // user -> User{ID: 111, Name: "Jinzhu", Age: 20}
// 未找到 user,根据条件和 Assign 属性创建记录 db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user) // SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1; // INSERT INTO "users" (name, age) VALUES ("non_existing", 20); // user -> User{ID: 112, Name: "non_existing", Age: 20}
// 找到了 `name` = `jinzhu` 的 user,则忽略 Attrs db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 20}).FirstOrCreate(&user) // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1; // user -> User{ID: 111, Name: "jinzhu", Age: 18}
不管是否找到记录,Assign 都会将属性赋值给 struct,并将结果写回数据库
// 未找到 user,根据条件和 Assign 属性创建记录 db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user) // SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1; // INSERT INTO "users" (name, age) VALUES ("non_existing", 20); // user -> User{ID: 112, Name: "non_existing", Age: 20}
// 找到了 `name` = `jinzhu` 的 user,依然会根据 Assign 更新记录 db.Where(User{Name: "jinzhu"}).Assign(User{Age: 20}).FirstOrCreate(&user) // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1; // UPDATE users SET age=20 WHERE id = 111; // user -> User{ID: 111, Name: "jinzhu", Age: 20}
db.Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find(&User{}) // SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`
索引提示允许传递索引提示到数据库,以防查询计划器出现混乱。
import"gorm.io/hints"
db.Clauses(hints.UseIndex("idx_user_name")).Find(&User{}) // SELECT * FROM `users` USE INDEX (`idx_user_name`)
db.Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find(&User{}) // SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"
var count int64 db.Model(&User{}).Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Count(&count) // SELECT count(1) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'
db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count) // SELECT count(1) FROM users WHERE name = 'jinzhu'; (count)
db.Table("deleted_users").Count(&count) // SELECT count(1) FROM deleted_users;
// Count with Distinct db.Model(&User{}).Distinct("name").Count(&count) // SELECT COUNT(DISTINCT(`name`)) FROM `users`
db.Table("deleted_users").Select("count(distinct(name))").Count(&count) // SELECT count(distinct(name)) FROM deleted_users
// Count with Group users := []User{ {Name: "name1"}, {Name: "name2"}, {Name: "name3"}, {Name: "name3"}, }