// works because destination struct is passed in db.First(&user) // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// works because model is specified using `db.Model()` result := map[string]interface{}{} db.Model(&User{}).First(&result) // SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
// doesn't work result := map[string]interface{}{} db.Table("users").First(&result)
// works with Take result := map[string]interface{}{} db.Table("users").Take(&result)
// no primary key defined, results will be ordered by first field (i.e., `Code`) type Language struct { Code string Name string } db.First(&Language{}) // SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1 , 注意这里会有一点 BUG
type User struct { ID string`gorm:"primarykey;size:16"` Name string`gorm:"size:24"` DeletedAt gorm.DeletedAt `gorm:"index"` }
var user = User{ID: 15} db.First(&user) // SELECT * FROM `users` WHERE `users`.`id` = '15' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
检索全部对象
Find
1 2 3 4 5 6
// Get all records result := db.Find(&users) // SELECT * FROM users;
result.RowsAffected // returns found records count, equals `len(users)` result.Error // returns error
Scan
Scanning results into a struct works similarly to the way we use Find
将结果输出到指定的 struct 中,工作方式类似 Find。
1 2 3 4 5 6 7 8 9 10
type Result struct { Name string Age int }
var result Result db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)
// Raw SQL db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)
// Get first matched record db.Where("name = ?", "jinzhu").First(&user) // SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;
// Get all matched records db.Where("name <> ?", "jinzhu").Find(&users) // SELECT * FROM users WHERE name <> 'jinzhu';
// IN db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users) // SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');
// LIKE db.Where("name LIKE ?", "%jin%").Find(&users) // SELECT * FROM users WHERE name LIKE '%jin%';
// AND db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users) // SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;
// Time db.Where("updated_at > ?", lastWeek).Find(&users) // SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';
// BETWEEN db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users) // SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';
如果对象设置了主键,条件查询将不会覆盖主键的值,而是用 And 连接条件。 例如:
1 2 3
var user = User{ID: 10} db.Where("id = ?", 20).First(&user) // SELECT * FROM users WHERE id = 10 and id = 20 ORDER BY id ASC LIMIT 1
这个查询将会给出record not found错误 所以,在你想要使用例如 user 这样的变量从数据库中获取新值前,需要将例如 id 这样的主键设置为nil。
Struct & Map 条件
1 2 3 4 5 6 7 8 9 10 11
// Struct db.Where(&User{Name: "jinzhu", Age: 20}).First(&user) // SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;
// Map db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 20;
// Slice of primary keys db.Where([]int64{20, 21, 22}).Find(&users) // SELECT * FROM users WHERE id IN (20, 21, 22);
NOTE When querying with struct, GORM will only query with non-zero fields, that means if your field’s value is 0, '', false or other zero values, it won’t be used to build query conditions, for example:
1 2
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu";
To include zero values in the query conditions, you can use a map, which will include all key-values as query conditions, for example:
1 2
db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
When searching with struct, you can specify which particular values from the struct to use in the query conditions by passing in the relevant field name or the dbname to Where(), for example:
1 2 3 4 5
db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users) // SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
db.Where(&User{Name: "jinzhu"}, "Age").Find(&users) // SELECT * FROM users WHERE age = 0;
内联条件
Query conditions can be inlined into methods like First and Find in a similar way to Where.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
// Get by primary key if it were a non-integer type db.First(&user, "id = ?", "string_primary_key") // SELECT * FROM users WHERE id = 'string_primary_key';
// Plain SQL db.Find(&user, "name = ?", "jinzhu") // SELECT * FROM users WHERE name = "jinzhu";
db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20) // SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;
// Struct db.Find(&users, User{Age: 20}) // SELECT * FROM users WHERE age = 20;
// Map db.Find(&users, map[string]interface{}{"age": 20}) // SELECT * FROM users WHERE age = 20;
Not 条件
Build NOT conditions, works similar to Where
1 2 3 4 5 6 7 8 9 10 11 12 13 14
db.Not("name = ?", "jinzhu").First(&user) // SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;
// Not In db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users) // SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");
// Struct db.Not(User{Name: "jinzhu", Age: 18}).First(&user) // SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;
// Not In slice of primary keys db.Not([]int64{1,2,3}).First(&user) // SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
Or 条件
1 2 3 4 5 6 7 8 9 10
db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users) // SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';
// Struct db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users) // SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);
// Map db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users) // SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);
db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result) // SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1
db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result) // SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows() defer rows.Close() for rows.Next() { ... }
rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows() defer rows.Close() for rows.Next() { ... }
type Result struct { Date time.Time Total int64 } db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)
Distinct
Selecting distinct values from the model
1
db.Distinct("name", "age").Order("name, age desc").Find(&results)
db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{}) // SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id
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)
// multiple joins with parameter 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)
Joins 预加载
You can use Joins eager loading associations with a single SQL, for example:
1 2 3 4 5 6
db.Joins("Company").Find(&users) // SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;
// inner join db.InnerJoins("Company").Find(&users) // SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` INNER JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;
Join with conditions
1 2
db.Joins("Company", db.Where(&Company{Alive: true})).Find(&users) // SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;
type Order struct { UserId int FinishedAt *time.Time }
query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id") db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results) // SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latest