|
|
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)
|
|
|
}
|
|
|
}
|
