【GoLang】《GORM实战》第三篇：关联与预加载

深碍√TFBOYSˉ_ 2023-10-02 16:28 34阅读 0赞

#### 文章目录 ####

*  关联
 *   *  分类
     *  重写外键、引用
     *  多态关联
     *  外键约束
     *  关联操作
     *   *  自动添加关联
         *  关联模式
         *   *  查询关联
             *  添加关联
             *  替换关联
             *  删除关联
             *  级联删除
             *  清空关联
             *  关联计数
             *  批量处理数据
 *  预加载
 *   *  Preload
     *   *  带条件的预加载
         *  预加载全部
         *  自定义预加载 SQL
         *  嵌套预加载
     *  Joins预加载

## 关联 ##

### 分类 ###

*  **belongs to**：会与另一个模型建立了一对一的连接。 这种模型的每一个实例都“属于”另一个模型的一个实例。比如：一个学员属于一个门派
 *  **has one**：与另一个模型建立一对一的关联，但它和一对一关系有些许不同。 这种关联表明一个模型的每个实例都包含或拥有另一个模型的一个实例。比如：一个学员拥有一个档案
 *  **has many**： 与另一个模型建立了一对多的连接。 不同于 `has one`，拥有者可以有零或多个关联模型。比如：一个学员有多个武器
 *  **many to many** ：表示多对多的关系，会在两个model中添加一张连接表。例如：每个学员拥有多个技能，每个技能可以被多个学员习得

type Student struct {
        
    	StudentNum  string `gorm:"size:16;comment:编号;not NULL;unique;index"`
    	StudentName string `gorm:"column:name;size:32;comment:学员姓名;not NULL"`
    	Gender      int    `gorm:"type:int;size:1;comment:性别：1-男；2-女;not NULL"`
    	GenderName  string `gorm:"-"`
    	Birthday    string `gorm:"size:16;comment:出生日期;not NULL"`
    	Address     string `gorm:"type:varchar(100);default:蜀山;comment:地址"`
    	//下面两种方式解决零值问题：
    	//Address     *string `gorm:"type:varchar(100);default:蜀山;comment:地址"`
    	//Address  sql.NullString `gorm:"type:varchar(100);default:蜀山;comment:地址"`
    
    	IgnoreMe int `gorm:"-"` // 忽略本字段
    
    	gorm.Model
    
    	//Belongs To（A 属于 B） ：会与另一个模型建立了一对一的连接。 这种模型的每一个实例都“属于”另一个模型的一个实例
    	//一个学员属于一个门派
    	SectID uint
    	Sect   Sect 
    
    	//has one（A 有一个 B）：一个学员有一条档案信息
    	Profile Profile
    
    	//has many（A 有很多 B）：一个学员有多个武器，每个武器只属于一个人
    	Arms []Arms `gorm:"foreignKey:Owner;references:StudentName"` //武器库成立比较久远，只能根据名称来做关联（重写外键、重写引用）
    
    	//many to many（多对多）：每个学员拥有多个技能，每个技能可以被多个学员习得
    	// 会在两个model中添加一张连接表students_skills
    	Skills []Skill `gorm:"many2many:students_skills;"`
    }
    
    type Sect struct {
        
    	ID         uint   `gorm:"primarykey"`
    	SectName   string `gorm:"type:varchar(32);comment:门派名称;not NULL;"`
    	Leader     string `gorm:"type:varchar(32);comment:掌门人;not NULL;"`
    	Address    string `gorm:"type:varchar(128);comment:门派地址;not NULL;"`
    	CreatedAt  time.Time
    	UpdatedAt  time.Time
    	DeleteFlag soft_delete.DeletedAt `gorm:"type:tinyint(1);default:0;softDelete:flag"`
    }
    
    //武器
    type Arms struct {
        
    	ID         uint   `gorm:"primarykey"`
    	Owner      string `gorm:"<-:create;comment:所有者;type:varchar(32)"`
    	ArmsName   string `gorm:"type:varchar(32);comment:武器名称;not NULL;"`
    	ArmsType   uint   `gorm:"comment:类型;type:tinyint(1)"`
    	ArmsLevel  uint   `gorm:"comment:级别;type:tinyint(1)"`
    	CreatedAt  time.Time
    	UpdatedAt  time.Time
    	DeleteFlag soft_delete.DeletedAt `gorm:"type:tinyint(1);default:0;softDelete:flag"`
    }
    
    type Profile struct {
        
    	ID               uint   `gorm:"primarykey"`
    	StudentID        int    `gorm:"comment:学生ID"`
    	Hometown         string `gorm:"size:128;comment:故乡"`
    	FamilyBackground string `gorm:"size:32;comment:家庭背景"`
    	Father           string `gorm:"type:varchar(32);comment:父亲"`
    	Mother           string `gorm:"type:varchar(32);comment:母亲"`
    	CreatedAt        time.Time
    	UpdatedAt        time.Time
    	DeleteFlag soft_delete.DeletedAt `gorm:"type:tinyint(1);default:0;softDelete:flag"`
    }
    
    type Skill struct {
        
    	//mysql中，类型需要和长度写一起，id字段如果需要使用自增主键，不能指定类型和长度（autoIncrement标签不起作用）
    	ID        int    `gorm:"primarykey"`
    	StudentID int    `gorm:"<-:create;comment:学员ID;type:int(8)"`
    	SkillName string `gorm:"type:varchar(32);comment:技能名称;not NULL;"`
    	//如果只指定读权限，不能写入和更新（bug？）
    	Teacher   string `gorm:"<-:create;->:false;comment:师父;type:varchar(32)"`
    	CreatedAt time.Time
    	UpdatedAt time.Time
    	/*
    		使用混合模式时，该字段如果设置为Time，Insert时会报错：Error 1292: Incorrect datetime value: '0000-00-00' for column 'deleted_at' at row 1
    		INSERT INTO `skills` (`skill_name`,`teacher`,`created_at`,`updated_at`,`deleted_at`,`is_del`,`student_id`) VALUES ('万剑归一1','酒剑仙1','2021-11-17 16:42:02.624','2021-11-17 16:42:02.624','0000-00-00 00:00:00','0',1)
    
    		所以，该字段只能是uint
    
    		一般情况下 删除时间可以不用记录，修改时间可以通过MySQL配置自动生成，最后的修改时间即为删除时间
    	*/
    	DeletedAt uint
    	//注意此处的softDelete标签，使用逗号分隔的 而原生的gorm标签是分号分隔
    	DeleteFlag soft_delete.DeletedAt `gorm:"type:tinyint(1);default:0;softDelete:flag,DeletedAtField:DeletedAt"`
    }

### 重写外键、引用 ###

可以使用标签：

*  foreignkey：重写外键，即重新定义关联表的外键字段；
 *  references：重写引用，即重新定义其他表关联时所使用的字段

如：

type User struct {
        
      gorm.Model
      Name       string     `sql:"index"`
      CreditCard CreditCard `gorm:"foreignkey:UserName;references:name"`
    }
    
    type CreditCard struct {
        
      gorm.Model
      Number   string
      UserName string
    }

User为主表，CreditCard为关联表，CreditCard中，使用UserName字段保存User中的Name字段作为外键

默认情况下，如果不手动指定，会使用：模型名+ID 作为外键，声明这个字段之后，会自动识别为外键，如：

type User struct {
        
      gorm.Model
      Name       string     `sql:"index"`
      CreditCard CreditCard
    }
    
    type CreditCard struct {
        
      gorm.Model
      Number   string
      UserID   uint
    }

CreditCard中用UserID保存User的主键，作为外键

### 多态关联 ###

GORM 为 `has one` 和 `has many` 提供了多态关联支持，它会将拥有者实体的表名、主键都保存到多态类型的字段中。

应用场景：当多张表同时关联一张表，需要在关联表中区分不同的主表

type Cat struct {
        
      ID    int
      Name  string
      Toy   Toy `gorm:"polymorphic:Owner;"`
    }
    
    type Dog struct {
        
      ID   int
      Name string
      Toy  Toy `gorm:"polymorphic:Owner;"`
    }
    
    type Toy struct {
        
      ID        int
      Name      string
      OwnerID   int
      OwnerType string
    }
    
    db.Create(&Dog{
        Name: "dog1", Toy: Toy{
        Name: "toy1"}})
    // INSERT INTO `dogs` (`name`) VALUES ("dog1")
    // INSERT INTO `toys` (`name`,`owner_id`,`owner_type`) VALUES ("toy1","1","dogs")

您可以使用标签 `polymorphicValue` 来更改多态类型的值，例如：

type Dog struct {
        
      ID   int
      Name string
      Toy  Toy `gorm:"polymorphic:Owner;polymorphicValue:master"`
    }
    
    type Toy struct {
        
      ID        int
      Name      string
      OwnerID   int
      OwnerType string
    }
    
    db.Create(&Dog{
        Name: "dog1", Toy: Toy{
        Name: "toy1"}})
    // INSERT INTO `dogs` (`name`) VALUES ("dog1")
    // INSERT INTO `toys` (`name`,`owner_id`,`owner_type`) VALUES ("toy1","1","master")

### 外键约束 ###

你可以通过标签 `constraint` 并带上 `OnUpdate`、`OnDelete` 实现外键约束，例如：

type User struct {
        
      gorm.Model
      CreditCard CreditCard `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
    }
    
    type CreditCard struct {
        
      gorm.Model
      Number string
      UserID uint
    }

### 关联操作 ###

#### 自动添加关联 ####

func add() {
        
    	db := database.GetDb()
    	student := entity.Student{
        
    		StudentNum:  "200001",
    		StudentName: "景天",
    		Gender:      1,
    		Birthday:    "仙剑元年前50年",
    		SectID:      1,
    		Profile: entity.Profile{
        
    			Address:  "渝州",
    			Hometown: "永安当",
    			Father:   "未知",
    			Mother:   "未知",
    		},
    		Arms: []entity.Arms{
        {
        
    			ArmsName:  "镇妖剑",
    			ArmsLevel: 10,
    			ArmsType:  1,
    		}, {
        
    			ArmsName:  "木剑",
    			ArmsLevel: 1,
    			ArmsType:  1,
    		}},
    		Skills: []entity.Skill{
        
    			{
        
    				SkillName: "飞龙探云手",
    				Teacher:   "小猴精",
    			},
    		},
    	}
    
    	//INSERT INTO `students` (`student_num`,`name`,`gender`,`birthday`,`created_at`,`updated_at`,`deleted_at`,`sect_id`) VALUES ('200001','景天',1,'仙剑元年前50年','2021-11-19 15:58:21.609','2021-11-19 15:58:21.609',NULL,1)
    
    	//INSERT INTO `profiles` (`student_id`,`hometown`,`address`,`father`,`mother`,`created_at`,`updated_at`,`delete_flag`) VALUES (1,'永安当','渝州','未知','未知','2021-11-19 15:58:21.614','2021-11-19 15:58:21.614',0) ON DUPLICATE KEY UPDATE `student_id`=VALUES(`student_id`)
    
    	//INSERT INTO `arms` (`owner`,`arms_name`,`arms_type`,`arms_level`,`created_at`,`updated_at`,`delete_flag`) VALUES ('景天','镇妖剑',1,10,'2021-11-19 15:58:21.619','2021-11-19 15:58:21.619',0),('景天','木剑',1,1,'2021-11-19 15:58:21.619','2021-11-19 15:58:21.619',0) ON DUPLICATE KEY UPDATE `owner`=VALUES(`owner`)
    
    	//INSERT INTO `skills` (`student_id`,`skill_name`,`teacher`,`created_at`,`updated_at`,`deleted_at`,`delete_flag`) VALUES (0,'飞龙探云手','小猴精','2021-11-19 15:58:21.624','2021-11-19 15:58:21.624',0,0) ON DUPLICATE KEY UPDATE `id`=`id`
    
    	//INSERT INTO `students_skills` (`student_id`,`skill_id`) VALUES (1,1) ON DUPLICATE KEY UPDATE `student_id`=`student_id`
    	db.Debug().Model(&entity.Student{
        }).Save(&student)

也可以通过**Select**或**Omit**方法来**指定**或**排除**字段来创建记录

Select：指定字段进行创建 (经测试，Select中只能选择一个字段，选多了就都不识别，所以慎用)

db.Debug().Select("StudentNum,StudentName").Create(&student)//指定多个字段时，不生效

![9a8945f0ebd0daa27bede427d4c5e271.png][]

db.Debug().Select("StudentNum").Create(&student)//只指定一个字段，不适用

![89873d58808d66f339f93bc885831573.png][]

Omit：排除指定字段进行创建

//INSERT INTO `arms` (`owner`,`arms_name`,`arms_type`,`arms_level`,`created_at`,`updated_at`,`delete_flag`) VALUES ('景天','镇妖剑',1,10,'2021-11-19 16:25:52.859','2021-11-19 16:25:52.859',0),('景天','木剑',1,1,'2021-11-19 16:25:52.859','2021-11-19 16:25:52.859',0) ON DUPLICATE KEY UPDATE `owner`=VALUES(`owner`)
    
    //INSERT INTO `students` (`student_num`,`name`,`gender`,`birthday`,`created_at`,`updated_at`,`deleted_at`,`sect_id`) VALUES ('200006','景天',1,'仙剑元年前50年','2021-11-19 16:25:52.853','2021-11-19 16:25:52.853',NULL,1)
    db.Debug().Omit("Skills,Profile").Create(&student) //跳过Skills和Profile的关联创建
    
    //INSERT INTO `students` (`student_num`,`name`,`gender`,`birthday`,`created_at`,`updated_at`,`deleted_at`,`sect_id`) VALUES ('200003','景天',1,'仙剑元年前50年','2021-11-19 16:23:15.714','2021-11-19 16:23:15.714',NULL,1)
    db.Debug().Omit(clause.Associations).Create(&student) //跳过自动创建所有关联记录

#### 关联模式 ####

关联模式包含一些在处理关系时有用的方法

func associationMode() {
        
    	db := database.GetDb()
    	// 开始关联模式
    	var student entity.Student
    	db.Model(&student).Association("Arms")
    	// `student` 是源模型，它的主键不能为空
    	// 关系的字段名是 `Arms`
    	// 如果上面两个条件匹配，会开始关联模式，否则会返回错误
    	e := db.Model(&student).Association("Arms").Error
    	if e != nil {
        
    		fmt.Println(e)
    	}
    }

##### 查询关联 #####

查找所有匹配的关联记录

var student = entity.Student{
        StudentName: "景天", Model: gorm.Model{
        ID: 1}}
    
    	var arms []entity.Arms
    	//SELECT * FROM `arms` WHERE `arms`.`owner` = '景天' AND `arms`.`delete_flag` = 0
    	db.Model(&student).Association("Arms").Find(&arms)
    
    	var profile entity.Profile
    	//SELECT * FROM `profiles` WHERE `profiles`.`student_id` = 1 AND `profiles`.`delete_flag` = 0
    	db.Debug().Model(&student).Association("Profile").Find(&profile)

##### 添加关联 #####

Append方法功能如下：

*  对于many to many、has many，添加新的关联
 *  对于has one、belongs to，替换当前关联

//INSERT INTO `arms` (`owner`,`arms_name`,`arms_type`,`arms_level`,`created_at`,`updated_at`,`delete_flag`) VALUES ('景天','木剑',1,1,'2021-11-19 19:32:13.742','2021-11-19 19:32:13.742',0) ON DUPLICATE KEY UPDATE `owner`=VALUES(`owner`)
    	db.Debug().Model(&student).Association("Arms").Append(&entity.Arms{
        ArmsType: 1, ArmsLevel: 1, ArmsName: "木剑"}) //has many：添加
    
    
    	//INSERT INTO `profiles` (`student_id`,`hometown`,`address`,`father`,`mother`,`created_at`,`updated_at`,`delete_flag`) VALUES (1,'神界','神魔之井','未知1','未知1','2021-11-19 19:33:42.065','2021-11-19 19:33:42.065',0) ON DUPLICATE KEY UPDATE `student_id`=VALUES(`student_id`)
    
    	//UPDATE `students` SET `updated_at`='2021-11-19 19:33:42.058' WHERE `id` = 1
    
    	//UPDATE `profiles` SET `student_id`=NULL WHERE `profiles`.`id` <> 2 AND `profiles`.`student_id` = 1 AND `profiles`.`delete_flag` = 0
    	db.Debug().Model(&student).Association("Profile").
    		Append(&entity.Profile{
        Hometown: "神界", Father: "未知1", Mother: "未知1", Address: "神魔之井"}) //has one:替换当前关联

可以看到：如果是many to many、has many则直接添加，如果是has one、belongs to，不会直接修改，二是先创建新的，再把之前的外键置为空

##### 替换关联 #####

这里经过测试，如果是has many的关系，会添加新的记录，不是做替换，可能是bug

//INSERT INTO `arms` (`owner`,`arms_name`,`arms_type`,`arms_level`,`created_at`,`updated_at`,`delete_flag`) VALUES ('景天','春滋剑',1,10,'2021-11-22 11:36:03.224','2021-11-22 11:36:03.224',0) ON DUPLICATE KEY UPDATE `owner`=VALUES(`owner`)
    	//UPDATE `students` SET `updated_at`='2021-11-22 11:36:03.209' WHERE `id` = 1
    	_ = db.Debug().Model(&student).Association("Arms").Replace(&entity.Arms{
        ArmsName: "春滋剑", ArmsLevel: 10, ArmsType: 1})
    
    	//INSERT INTO `arms` (`owner`,`arms_name`,`arms_type`,`arms_level`,`created_at`,`updated_at`,`delete_flag`) VALUES ('景天','春滋剑',1,10,'2021-11-22 11:40:50.771','2021-11-22 11:40:50.771',0),('景天','神剑',1,10,'2021-11-22 11:40:50.771','2021-11-22 11:40:50.771',0) ON DUPLICATE KEY UPDATE `owner`=VALUES(`owner`)
    
    //UPDATE `students` SET `updated_at`='2021-11-22 11:36:03.209' WHERE `id` = 1
    	_ = db.Debug().Model(&student).Association("Arms").Replace(&[]entity.Arms{
        {
        ArmsName: "春滋剑", ArmsLevel: 10, ArmsType: 1},{
        ArmsName: "神剑", ArmsLevel: 10, ArmsType: 1}})

![5a63ae3a4be6927f9c3d45c255127e92.png][]

正常来说，最后应该还会执行一个删除或者更新的SQL，重新查看结构体发现Arms中的外键字段owner的权限是只写，所以修改失败：

![ee79a9b4f3fb0424572e4a86591ca709.png][]

将限制：<-:create去掉之后，修改成功：

![832ac42d2106db7406a74e3bed206351.png][]

如果是many to many的关系，会添加新的记录，并删除之前的关联记录

//INSERT INTO `skills` (`skill_name`,`teacher`,`created_at`,`updated_at`,`deleted_at`,`delete_flag`) VALUES ('万剑归宗','酒剑仙','2021-11-22 14:51:53.415','2021-11-22 14:51:53.415',0,0),('飞龙探云手','李逍遥','2021-11-22 14:51:53.415','2021-11-22 14:51:53.415',0,0) ON DUPLICATE KEY UPDATE `id`=`id`
    
    	//INSERT INTO `students_skills` (`student_id`,`skill_id`) VALUES (1,2),(1,3) ON DUPLICATE KEY UPDATE `student_id`=`student_id`
    
    	//UPDATE `students` SET `updated_at`='2021-11-22 14:51:53.403' WHERE `id` = 1
    
    	//DELETE FROM `students_skills` WHERE `students_skills`.`student_id` = 1 AND `students_skills`.`skill_id` NOT IN (2,3)
    	_ = db.Debug().Model(&student).Association("Skills").Replace(&[]entity.Skill{
        {
        SkillName:"万剑归宗",Teacher:"酒剑仙"},{
        SkillName:"飞龙探云手",Teacher:"李逍遥"}})

如果是has one的关系，会执行替换操作

//INSERT INTO `profiles` (`student_id`,`hometown`,`address`,`father`,`mother`,`created_at`,`updated_at`,`delete_flag`) VALUES (1,'渝州城','永安当','神秘人','神秘人','2021-11-22 11:44:17.674','2021-11-22 11:44:17.674',0) ON DUPLICATE KEY UPDATE `student_id`=VALUES(`student_id`)
    
    	//UPDATE `students` SET `updated_at`='2021-11-22 11:44:17.668' WHERE `id` = 1
    
    	//UPDATE `profiles` SET `student_id`=NULL WHERE `profiles`.`id` <> 3 AND `profiles`.`student_id` = 1 AND `profiles`.`delete_flag` = 0
    _ = db.Debug().Model(&student).Association("Profile").Replace(&entity.Profile{
        Hometown: "渝州城", Address: "永安当", Father: "神秘人", Mother: "神秘人"})

##### 删除关联 #####

关系为has many：设置关联外键为null

//UPDATE `arms` SET `owner`=NULL WHERE `arms`.`owner` = '景天' AND `arms`.`id` IN (17,18) AND `arms`.`delete_flag` = 0
    	_ = db.Debug().Model(&student).Association("Arms").Delete([]entity.Arms{
        {
        ID: 17}, {
        ID: 18}})

关系为many to many：删除关联表数据

//DELETE FROM `students_skills` WHERE `students_skills`.`student_id` = 1 AND `students_skills`.`skill_id` IN (1,2)
    	_ = db.Debug().Model(&student).Association("Skills").Delete(&[]entity.Skill{
        {
        ID: 1}, {
        ID: 2}})

关系为has one：设置关联外键为null

//UPDATE `profiles` SET `student_id`=NULL WHERE `profiles`.`student_id` = 1 AND `profiles`.`id` = 1 AND `profiles`.`delete_flag` = 0
    	_ = db.Debug().Model(&student).Association("Profile").Delete(entity.Profile{
        ID: 1})

##### 级联删除 #####

var student = entity.Student{
        StudentName: "景天", Model: gorm.Model{
        ID: 3}}
    
    	// 删除学员时，删除档案信息
    	//UPDATE `profiles` SET `delete_flag`=1 WHERE `profiles`.`student_id` = 3 AND `profiles`.`delete_flag` = 0
    	//UPDATE `students` SET `deleted_at`='2021-11-22 17:56:27.137' WHERE `students`.`id` = 3 AND `students`.`deleted_at` IS NULL
    	db.Debug().Select("Profile").Delete(&student)
    
    	// 删除学员时，删除学员的武器、技能关联记录
    	//UPDATE `arms` SET `delete_flag`=1 WHERE `arms`.`owner` = '景天' AND `arms`.`delete_flag` = 0
    	//DELETE FROM `students_skills` WHERE `students_skills`.`student_id` = 3
    	//UPDATE `students` SET `deleted_at`='2021-11-22 17:57:49.149' WHERE `students`.`id` = 3 AND `students`.`deleted_at` IS NULL
    	db.Debug().Select("Arms", "Skills").Delete(&student)
    
    	// 删除学员时，删除所有has one/many/many2many 关系
    	/**
    	UPDATE `arms` SET `delete_flag`=1 WHERE `arms`.`owner` = '景天' AND `arms`.`delete_flag` = 0
    	DELETE FROM `students_skills` WHERE `students_skills`.`student_id` = 3
    	UPDATE `profiles` SET `delete_flag`=1 WHERE `profiles`.`student_id` = 3 AND `profiles`.`delete_flag` = 0
    	UPDATE `students` SET `deleted_at`='2021-11-22 17:59:23.932' WHERE `students`.`id` = 3 AND `students`.`deleted_at` IS NULL
    	*/
    	db.Debug().Select(clause.Associations).Delete(&student)
    
    	// 批量删除每个学员的档案
    	var students = []entity.Student{
        {
        StudentName: "景天", Model: gorm.Model{
        ID: 3}}, {
        StudentName: "李逍遥", Model: gorm.Model{
        ID: 1}}}
    	/**
    	UPDATE `profiles` SET `delete_flag`=1 WHERE `profiles`.`student_id` IN (3,1) AND `profiles`.`delete_flag` = 0
    	UPDATE `students` SET `deleted_at`='2021-11-22 18:00:57.062' WHERE `students`.`id` IN (3,1) AND `students`.`deleted_at` IS NULL
    	*/
    	db.Debug().Select("Profile").Delete(&students)

注意：只有当待删除的主体的主键非0时，关联的数据才会被删除

//没有指定主键 不会删除关联数据
    	//UPDATE `students` SET `deleted_at`='2021-11-22 18:09:15.68' WHERE name='李逍遥' AND `students`.`deleted_at` IS NULL
    	db.Debug().Select("Profile").Where("name=?", "李逍遥").Delete(&entity.Student{
        })
    
    	//即使指定多个条件，关联数据只根据主表id删除
    	/**
    	UPDATE `profiles` SET `delete_flag`=1 WHERE `profiles`.`student_id` = 1 AND `profiles`.`delete_flag` = 0
    	UPDATE `students` SET `deleted_at`='2021-11-22 18:10:14.729' WHERE name='李逍遥' AND `students`.`id` = 1 AND `students`.`deleted_at` IS NULL
    	*/
    	db.Debug().Select("Profile").Where("name=?", "李逍遥").Delete(&entity.Student{
        Model: gorm.Model{
        ID: 1}})

##### 清空关联 #####

many to many 会删除关联表记录，其他的是更新外键字段为null

//UPDATE `arms` SET `owner`=NULL WHERE `arms`.`owner` = '景天' AND `arms`.`delete_flag` = 0
    	_ = db.Debug().Model(&student).Association("Arms").Clear()
    
    	//DELETE FROM `students_skills` WHERE `students_skills`.`student_id` = 1
    	_ = db.Debug().Model(&student).Association("Skills").Clear()
    
    	//UPDATE `profiles` SET `student_id`=NULL WHERE `profiles`.`student_id` = 1 AND `profiles`.`delete_flag` = 0
    	_ = db.Debug().Model(&student).Association("Profile").Clear()

##### 关联计数 #####

//SELECT count(*) FROM `arms` WHERE `arms`.`owner` = '景天' AND `arms`.`delete_flag` = 0
    	_ = db.Debug().Model(&student).Association("Arms").Count()
    
    	//SELECT count(*) FROM `arms` WHERE arms_type=1 AND `arms`.`owner` = '景天' AND `arms`.`delete_flag` = 0
    	_ = db.Debug().Model(&student).Where("arms_type=?", 1).Association("Arms").Count()
    
    	//SELECT count(*) FROM `skills` JOIN `students_skills` ON `students_skills`.`skill_id` = `skills`.`id` AND `students_skills`.`student_id` = 1 WHERE `skills`.`delete_flag` = 0
    	_ = db.Debug().Model(&student).Association("Skills").Count()
    
    	//SELECT count(*) FROM `profiles` WHERE `profiles`.`student_id` = 1 AND `profiles`.`delete_flag` = 0
    	_ = db.Debug().Model(&student).Association("Profile").Count()

##### 批量处理数据 #####

查询多个学员的所有武器

//SELECT * FROM `arms` WHERE `arms`.`owner` IN ('景天','李逍遥') AND `arms`.`delete_flag` = 0
    	var students = []entity.Student{
        {
        StudentName: "景天"}, {
        StudentName: "李逍遥"}}
    	var arms []entity.Arms
    	_ = db.Debug().Model(&students).Association("Arms").Find(&arms)

## 预加载 ##

### Preload ###

var students []entity.Student
    /**
    	SELECT * FROM `students` WHERE `students`.`deleted_at` IS NULL
    	SELECT * FROM `students_skills` WHERE `students_skills`.`student_id` IN (4,5) //many to many
    	SELECT * FROM `profiles` WHERE `profiles`.`student_id` IN (4,5) AND `profiles`.`delete_flag` = 0 //has one
    	SELECT * FROM `arms` WHERE `arms`.`owner` IN ('月清疏','重楼') AND `arms`.`delete_flag` = 0 //has many
    	SELECT * FROM `sects` WHERE `sects`.`id` IN (2,3) AND `sects`.`delete_flag` = 0 //belongs to
    	*/
    	db.Debug().Preload("Skills").Preload("Profile").Preload("Sect").Preload("Arms").Find(&students)

#### 带条件的预加载 ####

GORM 允许带条件的 Preload 关联，类似于内联条件

/**
    	SELECT * FROM `students` WHERE `students`.`deleted_at` IS NULL
    	SELECT * FROM `arms` WHERE `arms`.`owner` IN ('月清疏','重楼') AND arms_level=10 AND `arms`.`delete_flag` = 0
    	 */
    	db.Debug().Preload("Arms", "arms_level=?", 10).Find(&students)

#### 预加载全部 ####

`clause.Associations` 也可以和 `Preload` 一起使用，它可以用来 `预加载` 全部关联，例如：

db.Preload(clause.Associations).Find(&students)

#### 自定义预加载 SQL ####

/**
    	SELECT * FROM `students` WHERE `students`.`deleted_at` IS NULL
    	SELECT * FROM `arms` WHERE (arms_level=10 and arms_type=1) AND `arms`.`owner` IN ('月清疏','重楼') AND `arms`.`delete_flag` = 0 ORDER BY id DESC
    	*/
    	db.Debug().Preload("Arms", func(db *gorm.DB) *gorm.DB {
        
    		return db.Where("arms_level=10 and arms_type=1").Order("id DESC")
    	}).Find(&students)

#### 嵌套预加载 ####

GORM 支持嵌套预加载，例如：

db.Preload("Orders.OrderItems.Product").Preload("CreditCard").Find(&users)
    
    // 自定义预加载 `Orders` 的条件
    // 这样，GORM 就不会加载不匹配的 order 记录
    db.Preload("Orders", "state = ?", "paid").Preload("Orders.OrderItems").Find(&users)

### Joins预加载 ###

`Join Preload` 适用于一对一的关系，例如： `has one`, `belongs to`

Preload用分开的SQL查询关联数据，而Joins Preload用一个内连接查询数据

/**
    	SELECT
    		`students`.`student_num`,
    		`students`.`name`,
    		`students`.`gender`,
    		`students`.`birthday`,
    		`students`.`id`,
    		`students`.`created_at`,
    		`students`.`updated_at`,
    		`students`.`deleted_at`,
    		`students`.`sect_id`,
    		`Profile`.`id` AS `Profile__id`,
    		`Profile`.`student_id` AS `Profile__student_id`,
    		`Profile`.`hometown` AS `Profile__hometown`,
    		`Profile`.`address` AS `Profile__address`,
    		`Profile`.`father` AS `Profile__father`,
    		`Profile`.`mother` AS `Profile__mother`,
    		`Profile`.`created_at` AS `Profile__created_at`,
    		`Profile`.`updated_at` AS `Profile__updated_at`,
    		`Profile`.`delete_flag` AS `Profile__delete_flag`
    	FROM
    		`students`
    		LEFT JOIN `profiles` `Profile` ON `students`.`id` = `Profile`.`student_id`
    	WHERE
    		`students`.`deleted_at` IS NULL
    	 */
    	db.Debug().Joins("Profile").Find(&students)

[9a8945f0ebd0daa27bede427d4c5e271.png]: https://img-blog.csdnimg.cn/img_convert/9a8945f0ebd0daa27bede427d4c5e271.png
[89873d58808d66f339f93bc885831573.png]: https://img-blog.csdnimg.cn/img_convert/89873d58808d66f339f93bc885831573.png
[5a63ae3a4be6927f9c3d45c255127e92.png]: https://img-blog.csdnimg.cn/img_convert/5a63ae3a4be6927f9c3d45c255127e92.png
[ee79a9b4f3fb0424572e4a86591ca709.png]: https://img-blog.csdnimg.cn/img_convert/ee79a9b4f3fb0424572e4a86591ca709.png
[832ac42d2106db7406a74e3bed206351.png]: https://img-blog.csdnimg.cn/img_convert/832ac42d2106db7406a74e3bed206351.png