gormExample/retrieve.go

package gormExample

import (
	"database/sql"
	"fmt"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"log"
	"time"
)

func GetByPk() {
	// migrate
	DB.AutoMigrate(&Content{}, &ContentStrPK{})

	// 查询单条
	////c := Content{}
	////if err := DB.First(&c, 10).Error; err != nil {
	////	log.Println(err)
	////}
	//c := Content{}
	//c.ID = 10
	//if err := DB.First(&c).Error; err != nil {
	//	log.Println(err)
	//}
	//// 字符串类型的主键
	//cStr := ContentStrPK{}
	//if err := DB.First(&cStr, "id = ?", "some pk").Error; err != nil {
	//	//if err := DB.First(&cStr, "some pk").Error; err != nil {
	//	log.Println(err)
	//}
	//
	//// 查询多条
	//var cs []Content
	//if err := DB.Find(&cs, []uint{10, 11, 12}).Error; err != nil {
	//	log.Println(err)
	//}
	// 字符串类型的主键
	var cStrs []ContentStrPK
	if err := DB.Find(&cStrs, []string{"some", "pk", "item"}).Error; err != nil {
		log.Println(err)
	}
}

func GetOne() {
	c := Content{}
	if err := DB.First(&c, "id > ?", 42).Error; err != nil {
		log.Println(err)
	}

	o := Content{}
	if err := DB.Last(&o, "id > ?", 42).Error; err != nil {
		log.Println(err)
	}

	n := Content{}
	if err := DB.Take(&n, "id > ?", 42).Error; err != nil {
		log.Println(err)
	}

	f := Content{}
	if err := DB.Limit(1).Find(&f, "id > ?", 42).Error; err != nil {
		log.Println(err)
	}

	fs := Content{}
	if err := DB.Find(&fs, "id > ?", 42).Error; err != nil {
		log.Println(err)
	}
}

func GetToMap() {
	// 单条
	c := map[string]any{} //map[string]interface{}{}
	if err := DB.Model(&Content{}).First(&c, 13).Error; err != nil {
		log.Println(err)
	}
	//fmt.Println(c["id"], c["id"].(uint) == 13)
	// 需要接口类型断言，才能继续处理
	if c["id"].(uint) == 13 {
		fmt.Println("id bingo")
	}
	// time类型的处理
	fmt.Println(c["created_at"])
	t, err := time.Parse("2006-01-02 15:04:05.000 -0700 CST", "2023-04-10 22:00:11.582 +0800 CST")
	if err != nil {
		log.Println(err)
	}
	if c["created_at"].(time.Time) == t {
		fmt.Println("created_at bingo")
	}

	// 多条
	var cs []map[string]any
	if err := DB.Model(&Content{}).Find(&cs, []uint{13, 14, 15}).Error; err != nil {
		log.Println(err)
	}
	for _, c := range cs {
		fmt.Println(c["id"].(uint), c["subject"].(string), c["created_at"].(time.Time))
	}
}

func GetPluck() {
	// 使用切片存储
	var subjects []sql.NullString
	//var subjects []string
	if err := DB.Model(&Content{}).Pluck("subject", &subjects).Error; err != nil {
		log.Println(err)
	}

	for _, subject := range subjects {
		//NullString的使用
		if subject.Valid {
			fmt.Println(subject.String)
		} else {
			fmt.Println("[NULL]")
		}

		//
		//fmt.Println(subject)
	}

}

func GetPluckExp() {
	// 使用切片存储，如果表达式可以保证NULL不会出现了，就可以不适用NullType了
	var subjects []string
	// 字段为表达式的结果
	if err := DB.Model(&Content{}).Pluck("concat(coalesce(subject, '[no subject]'), '-', likes)", &subjects).Error; err != nil {
		log.Println(err)
	}

	for _, subject := range subjects {
		fmt.Println(subject)
	}
}

func GetSelect() {
	var c Content

	// 基本的字段名
	//if err := DB.Select("subject", "likes").First(&c, 13).Error; err != nil {
	//	log.Fatalln(err)
	//}

	// 字段表达式
	if err := DB.Select("subject", "likes", "concat(subject,'-', views) AS sv").First(&c, 13).Error; err != nil {
		log.Fatalln(err)
	}

	fmt.Printf("%+v\n", c)
}

func GetDistinct() {
	var cs []Content

	// 基本的字段名
	if err := DB.Distinct("*").Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}

	fmt.Printf("%+v\n", cs)

}

func WhereMethod() {
	var cs []Content

	// inline条件，内联条件
	//if err := DB.Find(&cs, "likes > ? AND subject like ?", 100, "gorm%").Error; err != nil {
	//	log.Fatalln(err)
	//}
	// SELECT * FROM `msb_content` WHERE (likes > 100 AND subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL

	// Where,通常在动态拼凑条件时使用
	//query := DB.Where("likes > ?", 100)
	//subject := ""
	//// 当前用户输出subject，不为空字符串时，才拼凑subject条件
	//if subject != "" {
	//	query.Where("subject like ?", subject+"%")
	//}
	//if err := query.Find(&cs).Error; err != nil {
	//	log.Fatalln(err)
	//}
	//	SELECT * FROM `msb_content` WHERE likes > 100 AND `msb_content`.`deleted_at` IS NULL

	// OR 逻辑运算
	//query := DB.Where("likes > ?", 100)
	//subject := "gorm"
	//// 当前用户输出subject，不为空字符串时，才拼凑subject条件
	//if subject != "" {
	//	//query.Where("subject like ?", "subject"+"%")
	//	query.Or("subject like ?", subject+"%")
	//}
	//if err := query.Find(&cs).Error; err != nil {
	//	log.Fatalln(err)
	//}
	//	SELECT * FROM `msb_content` WHERE (likes > 100 OR subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL

	// Not 逻辑运算
	query := DB.Where("likes > ?", 100)
	subject := "gorm"
	// 当前用户输出subject，不为空字符串时，才拼凑subject条件
	if subject != "" {
		//query.Not("subject like ?", subject+"%")
		// SELECT * FROM `msb_content` WHERE likes > 100 AND NOT subject like 'gorm%' AND `msb_content`.`deleted_at` IS NULL

		query = query.Or(DB.Not("subject like ?", subject+"%"))
		// SELECT * FROM `msb_content` WHERE (likes > 100 OR NOT subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL

	}
	if err := query.Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}

}

func WhereType() {
	var cs []Content

	// 嵌套分组构建复杂的逻辑运算
	// (1 or 2) and (3 and (4 or 5))
	// 1 or 2
	//condA := DB.Where("likes > ?", 10).Or("likes <= ?", 100)
	// 3 and (4 or 5)
	//condB := DB.Where("views > ?", 20).Where(DB.Where("views <= ?", 200).Or("subject like ?", "gorm%"))
	//query := DB.Where(condA).Where(condB)
	// SELECT * FROM `msb_content` WHERE (likes > 10 OR likes <= 100) AND (views > 20 AND (views <= 200 OR subject like 'gorm%')) AND `msb_content`.`deleted_at` IS NULL

	// map构建条件, and, = in
	//query := DB.Where(map[string]any{
	//	"views": 100,
	//	"id":    []uint{1, 2, 3, 4, 5},
	//})
	// SELECT * FROM `msb_content` WHERE `id` IN (1,2,3,4,5) AND `views` = 100 AND `msb_content`.`deleted_at` IS NULL

	// struct条件构建, and, =
	query := DB.Where(Content{
		Views:   100,
		Subject: "GORM",
	})
	// SELECT * FROM `msb_content` WHERE `msb_content`.`subject` = 'GORM' AND `msb_content`.`views` = 100 AND `msb_content`.`deleted_at` IS NULL

	if err := query.Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}
}

func PlaceHolder() {
	var cs []Content

	// 匿名
	//query := DB.Where("likes = ? AND subject like ?", 100, "gorm%")

	// 具名，绑定名字sql.Named()结构
	//query := DB.Where("likes = @like AND subject like @subject", sql.Named("subject", "gorm%"), sql.Named("like", 100))
	// SELECT * FROM `msb_content` WHERE (likes = 100 AND subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL

	// gorm还支持使用map的形式具名绑定
	query := DB.Where("likes = @like AND subject like @subject", map[string]any{
		"subject": "gorm%",
		"like":    100,
	})
	// SELECT * FROM `msb_content` WHERE (likes = 100 AND subject like 'gorm%') AND `msb_content`.`deleted_at` IS NULL

	if err := query.Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}
}

func OrderBy() {
	var cs []Content

	ids := []uint{2, 3, 1}
	//query := DB.Order("FIELD(id, 2, 3, 1)")

	query := DB.Clauses(clause.OrderBy{
		Expression: clause.Expr{
			SQL:                "FIELD(id, ?)",
			Vars:               []any{ids},
			WithoutParentheses: true,
		},
	})
	// SELECT * FROM `msb_content` WHERE `msb_content`.`id` IN (2,3,1) AND `msb_content`.`deleted_at` IS NULL ORDER BY FIELD(id, 2,3,1)

	if err := query.Find(&cs, ids).Error; err != nil {
		log.Fatalln(err)
	}

	for _, c := range cs {
		fmt.Println(c.ID)
	}
}

// 定义分页必要数据结构
type Pager struct {
	Page, PageSize int
}

// 默认的值
const (
	DefaultPage     = 1
	DefaultPageSize = 12
)

// 翻页程序
func Pagination(pager Pager) {
	// 确定page, offset 和 pagesize

	page := DefaultPage
	if pager.Page != 0 {
		page = pager.Page
	}

	pagesize := DefaultPageSize
	if pager.PageSize != 0 {
		pagesize = pager.PageSize
	}

	// 计算offset
	// page, pagesize, offset
	// 1, 10, 0
	// 2, 10, 10
	// 3, 10, 20
	offset := pagesize * (page - 1)

	var cs []Content
	// SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL LIMIT 15 OFFSET 30
	if err := DB.Offset(offset).Limit(pagesize).Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}
}

// 用于得到func(db *gorm.DB) *gorm.DB类型函数
// 为什么不直接定义函数，因为需要func(db *gorm.DB) *gorm.DB与分页信息产生联系。
func Paginate(pager Pager) func(db *gorm.DB) *gorm.DB {
	// 计算page
	page := DefaultPage
	if pager.Page != 0 {
		page = pager.Page
	}

	// 计算pagesize
	pagesize := DefaultPageSize
	if pager.PageSize != 0 {
		pagesize = pager.PageSize
	}

	// 计算offset
	// page, pagesize, offset
	// 1, 10, 0
	// 2, 10, 10
	// 3, 10, 20
	offset := pagesize * (page - 1)

	return func(db *gorm.DB) *gorm.DB {
		// 使用闭包的变量，实现翻页的业务逻辑
		return db.Offset(offset).Limit(pagesize)
	}
}

// 测试重用的分页查询
func PaginationScope(pager Pager) {

	var cs []Content
	// SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL LIMIT 15 OFFSET 30
	if err := DB.Scopes(Paginate(pager)).Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}

	var ps []Post
	// SELECT * FROM `msb_post` WHERE `msb_content`.`deleted_at` IS NULL LIMIT 15 OFFSET 30
	if err := DB.Scopes(Paginate(pager)).Find(&ps).Error; err != nil {
		log.Fatalln(err)
	}
}

func Count(pager Pager) {

	// 集中的条件，用于统计数量和获取某页记录
	query := DB.Model(&Content{}).
		Where("likes > ?", 99)

	// total rows count
	var count int64
	if err := query.Count(&count).Error; err != nil {
		log.Fatalln(err)
	}
	// SELECT count(*) FROM `msb_content` WHERE likes > 99 AND `msb_content`.`deleted_at` IS NULL
	// 计算总页数 ceil( count / pagesize)

	// rows per page
	var cs []Content
	if err := query.Scopes(Paginate(pager)).Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}
	// SELECT * FROM `msb_content` WHERE likes > 99 AND `msb_content`.`deleted_at` IS NULL LIMIT 15 OFFSET 30
}

func GroupHaving() {
	DB.AutoMigrate(&Content{})

	// 定义查询结构类型
	type Result struct {
		// 分组字段
		AuthorID uint

		// 合计字段
		TotalViews int
		TotalLikes int
		AvgViews   float64
	}

	// 执行分组合计过滤查询
	var rs []Result
	if err := DB.Model(&Content{}).
		Select("author_id", "SUM(views) as total_views", "SUM(likes) as total_likes", "AVG(views) as avg_views").
		Group("author_id").Having("total_views > ?", 99).
		Find(&rs).Error; err != nil {
		log.Fatalln(err)
	}
	// SQL
	// SELECT `author_id`,SUM(views) as total_views,SUM(likes) as total_likes,AVG(views) as avg_views FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL GROUP BY `author_id` HAVING total_views > 99

}

func Iterator() {
	// 利用DB.Rows() 获取Rows对象
	rows, err := DB.Model(&Content{}).Rows()
	if err != nil {
		log.Fatalln(err)
	}
	// [rows:-] SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL

	// 注意：保证使用过后关闭rows结果集
	defer func() {
		_ = rows.Close()
	}()
	fmt.Println(rows)

	// 迭代的从Rows中扫描记录到模型
	for rows.Next() {
		// 还有记录存在与结果集中
		var c Content
		if err := DB.ScanRows(rows, &c); err != nil {
			log.Fatalln(err)
		}
		fmt.Println(c.Subject)
	}
}

func Locking() {
	var cs []Content

	if err := DB.
		Clauses(clause.Locking{Strength: "UPDATE"}).
		Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}
	// [4.998ms] [rows:42] SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL FOR UPDATE

	if err := DB.
		Clauses(clause.Locking{Strength: "SHARE"}).
		Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}
	//	[3.006ms] [rows:42] SELECT * FROM `msb_content` WHERE `msb_content`.`deleted_at` IS NULL FOR SHARE
}

func SubQuery() {
	// migrate
	DB.AutoMigrate(&Author{}, &Content{})

	// 条件型子查询
	//select * from content where author_id in (select id from author where status=0);
	// 子查询，不需要使用终结方法Find完成查询，只需要构建语句即可
	whereSubQuery := DB.Model(&Author{}).Select("id").Where("status = ?", 0)
	var cs []Content
	if err := DB.Where("author_id IN (?)", whereSubQuery).Find(&cs).Error; err != nil {
		log.Fatalln(err)
	}
	// [4.782ms] [rows:0] SELECT * FROM `msb_content` WHERE author_id IN (SELECT `id` FROM `msb_author` WHERE status = 0 AND `msb_author`.`deleted_at` IS NULL) AND `msb_content`.`deleted_at` IS NULL

	// from型子查询
	//select * from (select subject, likes from content where publish_time is null) as temp where likes > 10;
	fromSubQuery := DB.Model(&Content{}).Where("publish_time IS NULL").Select("subject", "likes")
	type Result struct {
		Subject string
		Likes   int
	}
	var rs []Result
	if err := DB.Table("(?) AS temp", fromSubQuery).
		Where("likes > ?", 10).
		Find(&rs).Error; err != nil {
		log.Fatalln(err)
	}
	// [3.800ms] [rows:17] SELECT * FROM (SELECT `subject`,`likes` FROM `msb_content` WHERE publish_time IS NULL AND `msb_content`.`deleted_at` IS NULL) AS temp WHERE likes > 10
}

func FindHook() {
	var c Content
	if err := DB.First(&c, 13).Error; err != nil {
		log.Fatalln(err)
	}

	fmt.Printf("%+v\n", c)
}