You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

398 lines
11 KiB

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

package gormExample
import (
"fmt"
"gorm.io/gorm/clause"
"log"
)
func StdAssocModel() {
// 利用migrate创建表
// 以及多对多的关联表
// 以及外键约束
if err := DB.AutoMigrate(&Author{}, &Essay{}, &Tag{}, &EssayMate{}); err != nil {
log.Fatalln(err)
}
// CREATE TABLE `msb_author` (
// `id` bigint unsigned NOT NULL AUTO_INCREMENT,
// `created_at` datetime(3) DEFAULT NULL,
// `updated_at` datetime(3) DEFAULT NULL,
// `deleted_at` datetime(3) DEFAULT NULL,
// `status` bigint DEFAULT NULL,
// `name` longtext,
// `email` longtext,
// PRIMARY KEY (`id`),
// KEY `idx_msb_author_deleted_at` (`deleted_at`)
//) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
// CREATE TABLE `msb_essay` (
// `id` bigint unsigned NOT NULL AUTO_INCREMENT,
// `created_at` datetime(3) DEFAULT NULL,
// `updated_at` datetime(3) DEFAULT NULL,
// `deleted_at` datetime(3) DEFAULT NULL,
// `subject` longtext,
// `content` longtext,
// `author_id` bigint unsigned DEFAULT NULL,
// PRIMARY KEY (`id`),
// KEY `idx_msb_essay_deleted_at` (`deleted_at`),
// KEY `fk_msb_author_essays` (`author_id`),
// CONSTRAINT `fk_msb_author_essays` FOREIGN KEY (`author_id`) REFERENCES `msb_author` (`id`)
//) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
// CREATE TABLE `msb_essay_mate` (
// `id` bigint unsigned NOT NULL AUTO_INCREMENT,
// `created_at` datetime(3) DEFAULT NULL,
// `updated_at` datetime(3) DEFAULT NULL,
// `deleted_at` datetime(3) DEFAULT NULL,
// `keyword` longtext,
// `description` longtext,
// `essay_id` bigint unsigned DEFAULT NULL,
// PRIMARY KEY (`id`),
// KEY `idx_msb_essay_mate_deleted_at` (`deleted_at`),
// KEY `fk_msb_essay_essay_mate` (`essay_id`),
// CONSTRAINT `fk_msb_essay_essay_mate` FOREIGN KEY (`essay_id`) REFERENCES `msb_essay` (`id`)
//) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
// CREATE TABLE `msb_tag` (
// `id` bigint unsigned NOT NULL AUTO_INCREMENT,
// `created_at` datetime(3) DEFAULT NULL,
// `updated_at` datetime(3) DEFAULT NULL,
// `deleted_at` datetime(3) DEFAULT NULL,
// `title` longtext,
// PRIMARY KEY (`id`),
// KEY `idx_msb_tag_deleted_at` (`deleted_at`)
//) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
// CREATE TABLE `msb_essay_tag` (
// `tag_id` bigint unsigned NOT NULL,
// `essay_id` bigint unsigned NOT NULL,
// PRIMARY KEY (`tag_id`,`essay_id`),
// KEY `fk_msb_essay_tag_essay` (`essay_id`),
// CONSTRAINT `fk_msb_essay_tag_essay` FOREIGN KEY (`essay_id`) REFERENCES `msb_essay` (`id`),
// CONSTRAINT `fk_msb_essay_tag_tag` FOREIGN KEY (`tag_id`) REFERENCES `msb_tag` (`id`)
//) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
log.Println("migrate successful")
}
// 添加关联
func AssocAppend() {
// A一对多的关系, Author 1:n Essay
// 创建测试数据
var a Author
a.Name = "一位作者"
if err := DB.Create(&a).Error; err != nil {
log.Println(err)
}
log.Println("a:", a.ID)
var e1, e2 Essay
e1.Subject = "一篇内容"
//e1.AuthorID = a.ID
e2.Subject = "另一篇内容"
if err := DB.Create([]*Essay{&e1, &e2}).Error; err != nil {
log.Println(err)
}
log.Println("e1, e2: ", e1.ID, e2.ID)
// 添加关联
if err := DB.Model(&a).Association("Essays").Append([]Essay{e1}); err != nil {
log.Println(err)
}
fmt.Println(len(a.Essays))
// 基于当前的基础上,添加关联
if err := DB.Model(&a).Association("Essays").Append([]Essay{e2}); err != nil {
log.Println(err)
}
fmt.Println(len(a.Essays))
// 添加后a模型对象的Essays字段自动包含了关联的Essay模型
//fmt.Println(a.Essays)
// B: Essay M:N TAg
var t1, t2, t3 Tag
t1.Title = "Go"
t2.Title = "GORM"
t3.Title = "Ma"
if err := DB.Create([]*Tag{&t1, &t2, &t3}).Error; err != nil {
log.Println(err)
}
log.Println("t1, t2, t3: ", t1.ID, t2.ID, t3.ID)
// e1 t1, t3
// e2 t1, t2, t3
if err := DB.Model(&e1).Association("Tags").Append([]Tag{t1, t3}); err != nil {
log.Println(err)
}
if err := DB.Model(&e2).Association("Tags").Append([]Tag{t1, t2, t3}); err != nil {
log.Println(err)
}
// 关联表查看
// mysql> select * from msb_essay_tag;
//+--------+----------+
//| tag_id | essay_id |
//+--------+----------+
//| 1 | 12 |
//| 3 | 12 |
//| 1 | 13 |
//| 2 | 13 |
//| 3 | 13 |
//+--------+----------+
// C, Belongs To. Essay N:1 Author
var e3 Essay
e3.Subject = "第三篇内容"
if err := DB.Create([]*Essay{&e3}).Error; err != nil {
log.Println(err)
}
log.Println("e3: ", e3.ID)
log.Println(e3.Author)
// 关联
if err := DB.Model(&e3).Association("Author").Append(&a); err != nil {
log.Println(err)
}
log.Println(e3.Author.ID)
// 对一的关联,会导致关联被更新
var a2 Author
a2.Name = "另一位作者"
if err := DB.Create(&a2).Error; err != nil {
log.Println(err)
}
log.Println("a2:", a2.ID)
if err := DB.Model(&e3).Association("Author").Append(&a2); err != nil {
log.Println(err)
}
log.Println(e3.Author.ID)
}
func AssocReplace() {
// A. 替换
// 创建测试数据
var a Author
a.Name = "一位作者"
if err := DB.Create(&a).Error; err != nil {
log.Println(err)
}
log.Println("a:", a.ID)
var e1, e2, e3 Essay
e1.Subject = "一篇内容"
e2.Subject = "另一篇内容"
e3.Subject = "第三篇内容"
if err := DB.Create([]*Essay{&e1, &e2, &e3}).Error; err != nil {
log.Println(err)
}
log.Println("e1, e2, e3: ", e1.ID, e2.ID, e3.ID)
// 添加关联
if err := DB.Model(&a).Association("Essays").Replace([]Essay{e1, e3}); err != nil {
log.Println(err)
}
fmt.Println(len(a.Essays))
// 基于当前的基础上,添加关联
if err := DB.Model(&a).Association("Essays").Replace([]Essay{e2, e3}); err != nil {
log.Println(err)
}
fmt.Println(len(a.Essays))
}
func AssocDelete() {
// B. 删除,外键的
// 创建测试数据
var a Author
a.Name = "一位作者"
if err := DB.Create(&a).Error; err != nil {
log.Println(err)
}
log.Println("a:", a.ID)
var e1, e2, e3 Essay
e1.Subject = "一篇内容"
e2.Subject = "另一篇内容"
e3.Subject = "第三篇内容"
if err := DB.Create([]*Essay{&e1, &e2, &e3}).Error; err != nil {
log.Println(err)
}
log.Println("e1, e2, e3: ", e1.ID, e2.ID, e3.ID)
// 添加关联
if err := DB.Model(&a).Association("Essays").Replace([]Essay{e1, e2, e3}); err != nil {
log.Println(err)
}
fmt.Println(len(a.Essays))
if err := DB.Model(&a).Association("Essays").Delete([]Essay{e1, e3}); err != nil {
log.Println(err)
}
fmt.Println(len(a.Essays))
fmt.Println("------------------------")
// B. 删除,多对多,关联表
var t1, t2, t3 Tag
t1.Title = "Go"
t2.Title = "GORM"
t3.Title = "Ma"
if err := DB.Create([]*Tag{&t1, &t2, &t3}).Error; err != nil {
log.Println(err)
}
log.Println("t1, t2, t3: ", t1.ID, t2.ID, t3.ID)
// e1 t1, t3
// e2 t1, t2, t3
if err := DB.Model(&e1).Association("Tags").Append([]Tag{t1, t2, t3}); err != nil {
log.Println(err)
}
fmt.Println(len(e1.Tags))
if err := DB.Model(&e1).Association("Tags").Delete([]Tag{t1, t3}); err != nil {
log.Println(err)
}
fmt.Println(len(e1.Tags))
// C. 清空关联
if err := DB.Model(&e1).Association("Tags").Clear(); err != nil {
log.Println(err)
}
fmt.Println(len(e1.Tags))
}
func AssocFind() {
//
e := Essay{}
DB.First(&e, 18)
// 查询关联的tags
//var ts []Tag
if err := DB.Model(&e).Association("Tags").Find(&e.Tags); err != nil {
log.Println(err)
}
log.Println(e.Tags)
// 子句要写在Association()方法前面
if err := DB.Model(&e).
Where("tag_id > ?", 7).
Order("tag_id DESC").
Association("Tags").Find(&e.Tags); err != nil {
log.Println(err)
}
log.Println(e.Tags)
// 查询关联的模型的数量
count := DB.Model(&e).Association("Tags").Count()
log.Println("count:", count)
}
func AssocSave() {
var t1 Tag
DB.First(&t1, 10)
e := Essay{
Subject: "一个组合的Save",
Author: Author{Name: "马士兵"},
Tags: []Tag{
t1,
{Title: "Ma"},
{Title: "GORM"},
},
}
if err := DB.Save(&e).Error; err != nil {
log.Println(err)
}
log.Printf("%+v\n", e)
}
// Preload
func AssocPreload() {
// A.直接一步查询Author对应的Essays
a := Author{}
if err := DB.
Preload("Essays").
First(&a, 1).Error; err != nil {
log.Fatalln(err)
}
// [3.840ms] [rows:2] SELECT * FROM `msb_essay` WHERE `msb_essay`.`author_id` = 1 AND `msb_essay`.`deleted_at` IS NULL
// [13.014ms] [rows:1] SELECT * FROM `msb_author` WHERE `msb_author`.`id` = 1 AND `msb_author`.`deleted_at` IS NULL ORDER BY `msb_author`.`id` LIMIT 1
log.Println(a.Essays)
log.Println("--------------------")
// B.支持条件过滤
if err := DB.
Preload("Essays", "id IN ?", []uint{2, 3, 4}).
First(&a, 1).Error; err != nil {
log.Fatalln(err)
}
// [3.217ms] [rows:1] SELECT * FROM `msb_essay` WHERE `msb_essay`.`author_id` = 1 AND id IN (2,3,4) AND `msb_essay`.`deleted_at` IS NULL
log.Println(a.Essays)
log.Println("-----------------------")
// C. 支持多次链式调用,同时预加载多个关联
e := Essay{}
if err := DB.
//Preload("Author").
//Preload("EssayMate").
//Preload("Tags").
Preload(clause.Associations).
First(&e, 1).Error; err != nil {
log.Fatalln(err)
}
log.Println(e)
// [2.776ms] [rows:1] SELECT * FROM `msb_author` WHERE `msb_author`.`id` = 1 AND `msb_author`.`deleted_at` IS NULL
// [10.398ms] [rows:0] SELECT * FROM `msb_essay_mate` WHERE `msb_essay_mate`.`essay_id` = 1 AND `msb_essay_mate`.`deleted_at` IS NULL
// [3.260ms] [rows:2] SELECT * FROM `msb_essay_tag` WHERE `msb_essay_tag`.`essay_id` = 1
// [3.264ms] [rows:2] SELECT * FROM `msb_tag` WHERE `msb_tag`.`id` IN (1,3) AND `msb_tag`.`deleted_at` IS NULL
// [28.067ms] [rows:1] SELECT * FROM `msb_essay` WHERE `msb_essay`.`id` = 1 AND `msb_essay`.`deleted_at` IS NULL ORDER BY `msb_essay`.`id` LIMIT 1
}
// 多级
func AssocLevelPreload() {
a := Author{}
if err := DB.
//Preload("Essays").
// 多级关联
Preload("Essays.Tags").
First(&a, 1).Error; err != nil {
log.Fatalln(err)
}
// [3.843ms] [rows:5] SELECT * FROM `msb_essay_tag` WHERE `msb_essay_tag`.`essay_id` IN (1,2)
// [3.284ms] [rows:3] SELECT * FROM `msb_tag` WHERE `msb_tag`.`id` IN (1,3,2) AND `msb_tag`.`deleted_at` IS NULL
// [10.396ms] [rows:2] SELECT * FROM `msb_essay` WHERE `msb_essay`.`author_id` = 1 AND `msb_essay`.`deleted_at` IS NULL
// [17.609ms] [rows:1] SELECT * FROM `msb_author` WHERE `msb_author`.`id` = 1 AND `msb_author`.`deleted_at` IS NULL ORDER BY `msb_author`.`id` LIMIT 1
log.Println(a.Essays[0].Tags)
log.Println(a.Essays[1].Tags)
}
func AssocOperate() {
// 创建测试数据
var a Author
a.Name = "一位作者"
if err := DB.Create(&a).Error; err != nil {
log.Println(err)
}
log.Println("a:", a.ID)
var e1, e2 Essay
e1.Subject = "一篇内容"
//e1.AuthorID = a.ID
e2.Subject = "另一篇内容"
if err := DB.Create([]*Essay{&e1, &e2}).Error; err != nil {
log.Println(err)
}
log.Println("e1, e2: ", e1.ID, e2.ID)
// 基于当前的基础上,添加关联
if err := DB.Model(&a).Association("Essays").Append([]Essay{e1, e2}); err != nil {
log.Println(err)
}
fmt.Println(len(a.Essays))
// 删除a,注意essays的处理
if err := DB.Unscoped().Delete(&a).Error; err != nil {
log.Fatalln(err)
}
}