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で指定された属性で構造体を初期化 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で指定された属性で構造体を初期化 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` のUserが見つかったため、取得レコードの値と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 not found, create a new record with give conditions result := db.FirstOrCreate(&user, User{Name: "non_existing"}) // INSERT INTO "users" (name) VALUES ("non_existing"); // user -> User{ID: 112, Name: "non_existing"} // result.RowsAffected // => 1
// Found user with `name` = `jinzhu` result := db.Where(User{Name: "jinzhu"}).FirstOrCreate(&user) // user -> User{ID: 111, Name: "jinzhu", "Age": 18} // result.RowsAffected // => 0
// Userが見つからないため、取得条件とAttrsで指定された属性でレコードを作成 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`のユーザが見つかったため、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}
// 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.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`)"
for rows.Next() { var user User // ScanRows is a method of `gorm.DB`, it can be used to scan a row into a struct db.ScanRows(rows, &user)
// do something }
FindInBatches
バッチ処理におけるクエリやレコード処理を行うことができます。
// batch size 100 result := db.Where("processed = ?", false).FindInBatches(&results, 100, func(tx *gorm.DB, batch int)error { for _, result := range results { // batch processing found records }
tx.Save(&results)
tx.RowsAffected // number of records in this batch
batch // Batch 1, 2, 3
// returns error will stop future batches returnnil })
result.Error // returned error result.RowsAffected // processed records count in all batches
db.Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find(&orders) // Find all credit card orders and amount greater than 1000
db.Scopes(AmountGreaterThan1000, PaidWithCod).Find(&orders) // Find all COD orders and amount greater than 1000
db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders) // Find all paid, shipped orders that amount greater than 1000
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"}, }