mx52jing

Appearance

Return to top
On this page

Advanced Query

智能选择字段

  • 选择字段可以使用Select，但是如果有多个Select操作，可以定义一个结构体，查询的时候会自动检索结构体中的字段
Go
type SmartFields struct {
	Name   string
	Age    int
	Gender int
}

var smartT []SmartFields
DB.Model(&Teacher{}).Find(&smartT)
fmt.Println("smartT is=>", smartT)
// SELECT `teachers`.`name`,`teachers`.`age`,`teachers`.`gender` FROM `teachers`

子查询

  • 使用上次查询的结果作为本次查询的参数
Go
var subQueryT []Teacher
DB.Where("age >= (?)", DB.Model(&Teacher{}).Select("AVG(age)")).Find(&subQueryT)
fmt.Println("subQueryT is=>", subQueryT)
// SELECT * FROM `teachers` WHERE age >= (SELECT AVG(age) FROM `teachers`)

FROM子查询

  • 可以在Table方法中通过FROM子句使用子查询
Go
DB.Table("(?) as t", DB.Model(&Teacher{}).Select("name", "age")).Where("age > ?", 18).Find(&subQueryT)
// SELECT * FROM (SELECT `name`,`age` FROM `teachers`) as t WHERE age > 18

命名参数

  • GORM支持sql.NamedArg和map[string]interface{}{}形式的命名参数
Go
DB.Where("name1 = @name OR name2 = @name", sql.Named("name", "如燕")).Find(&namedQueryT)
// SELECT * FROM `teachers` WHERE name1 = '如燕' OR name2 = '如燕'

DB.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "如燕"}).Find(&namedQueryT)
// SELECT * FROM `teachers` WHERE name1 = '如燕' OR name2 = '如燕'

Find到Map中

  • GORM允许扫描结果至map[string]interface{}或者[]map[string]interface{}
Go
var res map[string]interface{}
DB.Model(&Teacher{}).Where("age >= ?", 18).Take(&res)
// SELECT * FROM `teachers` WHERE age >= 18 LIMIT 1

var res []map[string]interface{}
DB.Table("teachers").Where("age >= ?", 18).Find(&res)
// SELECT * FROM `teachers` WHERE age >= 18

WARNING

使用该查询方法一定要指定Model或者Table

Scopes

  • Scopes允许指定一些常用的查询，可以在调用方法时引用这些查询
Go
func ageGreaterThan18(db *gorm.DB) *gorm.DB {
	return db.Where("age > ?", 18)
}

func nameLikeMul(db *gorm.DB) *gorm.DB {
	return db.Where("name LIKE ?", "张%")
}

var scopeT Teacher
DB.Scopes(ageGreaterThan18).Find(&scopeT)
// SELECT * FROM `teachers` WHERE age > 18

var scopeT []Teacher
DB.Scopes(ageGreaterThan18, nameLikeMul).Find(&scopeT)
// SELECT * FROM `teachers` WHERE age > 18 AND name LIKE '张%'

Count

  • 展示查询结果的条数

  • 查询出的结果count是int64类型

Go
var count int64
DB.Model(&Teacher{}).Where("age >= ?", 18).Count(&count)
// SELECT count(*) FROM `teachers` WHERE age >= 18