• 【最佳实践】gorm 联表查询 joins

    内容

    • 使用gorm的一些技巧、经验
    • 常规使用示例
    • 如何在一对一、一对多、多对一的关系下使用gorm进行联表查询
      其他gorm使用和进阶用法参考官方文档 https://gorm.io/zh_CN/docs/index.html

    优雅表迁移注册

    1. 表自动迁移方式，常见的方式如下：
    #model层定义model
package models
type Cluster struct{

}
#global层创建gorm.db对象，并注册迁移表结构
package global
func AutoMigrate() (err error) {
	if err = db.AutoMigrate(
		dbms.Cluster{},
		dbms.Instance{},
		dbms.Account{},
	); err != nil {
		return errors.Wrap(err, "创建数据库表失败")
	}
	return nil
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    这种显示调用方式的缺点是每次新建表结构都要去找global 层的迁移方法并修改，以下推荐一种隐式迁移表结构，通过创建一个迁移全局对象，并把表结构注册分别注册到对象中，最后db.AutoMigrat调用全局变量即可。

    #model层定义model和创建全局迁移表对象
package models
var AddMigrate []interface{} 

type Cluster struct{

}

func init() {  // 包初始化后默认注册待迁移表对象
	AddMigrate = append(AddMigrate, Cluster{})
}

#global层创建gorm.db对象，并调用全局迁移表对象
package global
func AutoMigrate() (err error) {
	if err = db.AutoMigrate(model.AddMigrate...); err != nil {
		return errors.Wrap(err, "创建数据库表失败")
	}
	return nil
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    常规操作示例

    first、last、take、find区别

    • first会进行排序，主键升序；检索单个对象，LIMIT 1;not found会报错
    • last会进行排序，主键降序；检索单个对象，LIMIT 1;not found会报错
    • take不会进行排序；检索单个对象，LIMIT 1;not found会报错
    • find 获取所有对象；not found 不会报错

    查询find

    r := s.DB.Model(&models.Cluster{}).
		Where(&models.Cluster{Name: req.Name, EnvCode: req.EnvCode, ClusterType: req.ClusterType}).
		Count(&total).Find(&res)
	if r.Error != nil {
		return nil, 0, errors.Annotate(r.Error, "查询数据库失败")
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    查询take

    err := s.DB.Where(&models.Instance{ID: req.ID}).Take(&ins).Error


    • 1
    • 2

    create单个记录

    if err := s.DB.Create(&models.Cluster{
		Name:          cluster.Name,
		EnvCode:       cluster.EnvCode,
		MasterVipHost: cluster.MasterVipHost,
	}).Error; err != nil {
		return errors.Annotate(err, "创建记录失败")
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    事务性批量插入

    	if err = db.CreateInBatches(list, len(list)).Error; err != nil {
		return errors.Annotate(err, "批量创建账号记录失败")
	}


    • 1
    • 2
    • 3
    • 4

    一次性批量插入

    if err = db.Create(list).Error; err != nil {
		return errors.Annotate(err, "批量创建账号记录失败")
	}

    • 1
    • 2
    • 3

    delete

    	if err = s.DB.Delete(&models.Cluster{ID: id}).Error; err != nil {
		return errors.Annotate(err, "删除记录失败")
	}

    • 1
    • 2
    • 3

    查询某个字段

    	var total int64
	err := s.DB.Model(&models.Instance{}).Where("cluster_id = ?", id).Count(&total).Error
	if err != nil {
		return errors.Annotate(err, "查询数据库异常")
	}

或者
	var total int64
	err := s.DB.Model(&models.Instance{}).Select("count(*)").Where("cluster_id = ?", id).take(&total).Error
	if err != nil {
		return errors.Annotate(err, "查询数据库异常")
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    更新某些字段，推荐指定where条件

    	if err = s.DB.Where("id = ?",req.ID).Updates(&models.Instance{
		Name:           req.Name,
		InstanceHost:   req.InstanceHost,
		InstancePort:   req.InstancePort,
		ManageUser:     req.ManageUser,
		ManagePassword: req.ManagePassword,
		ClusterRole:    req.ClusterRole,
		Description:    req.Description,
	}).Error; err != nil {
		return errors.Annotate(err, "更新记录失败")
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    hook简单使用

    可以通过gorm提供的hook和context结合，实现变更时自动更新user信息

    // 通过controller层传递带有用户信息的context到services层，用gorm.session创建新的db

// controller层保存用户信息到context
context.WithValue(ctx, Cookie, cookie)

//controller层传递ctx
services.NewInstanceService(c.Context()).GetInstanceDetail(id)

//services层
DB:   global.DB.WithContext(ctx),

// 模型注册hook
func (m *Instance) BeforeCreate(tx *gorm.DB) (err error) {
	m.CreateUser = sso.GetNameFromCtx(tx.Statement.Context)
	return nil
}

func (m *Instance) BeforeUpdate(tx *gorm.DB) (err error) {
	m.ModifyUser = sso.GetNameFromCtx(tx.Statement.Context)
	return nil
}

func (m *Instance) BeforeDelete(tx *gorm.DB) (err error) {
	m.ModifyUser = sso.GetNameFromCtx(tx.Statement.Context)
	return nil
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    联表查询实例

    以下示例主要实践两个表的关联查询，更多、更复杂的关联查询建议直接使用原生sql进行查询。

    关联模型定义

    表关联，常用使用方式，以下

    • 通过定义表名同名的模型，并指定关联外键，如下
    // instance与cluster关联关系 instance belongs to cluster
type Instance struct {
	ID              int    `json:"id" gorm:"primaryKey;autoIncrement;column:id;comment:自增id"`
	Name            string `json:"name" gorm:"type:varchar(64);not null;comment:集群名"`
	InstanceHost    string `json:"instanceHost" gorm:"type:varchar(64);not null;comment:实例连接地址"`
	InstancePort    string `json:"instancePort" gorm:"type:varchar(64);not null;comment:实例连接端口"`
	InstanceVersion string `json:"instanceVersion" gorm:"type:varchar(64);not null;comment:实例版本"`
	ClusterID      int64  `json:"clusterId" gorm:"type:int(11);not null;default:0;comment:关联表cluster"` // 指定关联外键，belongs to关系
	Cluster        Cluster `json:"cluster,omitempty"` // belongs to关系
	Model `json:",omitempty"`
}

type Cluster struct {
	ID            int64  `json:"id" gorm:"primaryKey;autoIncrement;column:id;comment:自增id"`
	Name          string `json:"name" gorm:"type:varchar(64);not null;default:;comment:集群名"`
	EnvCode       string `json:"envCode" gorm:"type:varchar(64);not null;default:;comment:环境code"`
	Model `json:",omitempty"`
}

// 用法 关联查看instance和cluster表
var list []Instance
res := gorm.DB.Joins("Cluster").Where("cluster_id = 1").where(&instance{Name: "test"}).Find(&list) // 注意joins中必须是关联的结构体名，不能是表名
if res.Error != nil {
	return errors.New(err,"查询记录失败")
}
fmt.Println(list)
// select * from dbms_instance LEFT JOIN `dbms_cluster` `Cluster` ON `dbms_instance`.`cluster_id` = `Cluster`.`id` AND `Cluster`.`deleted` = 0 WHERE `dbms_instance`.`name` = "test" AND `dbms_instance`.`deleted` = 0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    其他关联关系用法类型

    • 通过手动创建结构体模型承载返回数据，查询时需要指定关联表和关联关系，示例如下
    // 模型定义
package models
type Instance struct {
	ID              int    `json:"id" gorm:"primaryKey;autoIncrement;column:id;comment:自增id"`
	Name            string `json:"name" gorm:"type:varchar(64);not null;comment:集群名"`
	InstanceHost    string `json:"instanceHost" gorm:"type:varchar(64);not null;comment:实例连接地址"`
	InstancePort    string `json:"instancePort" gorm:"type:varchar(64);not null;comment:实例连接端口"`
	InstanceVersion string `json:"instanceVersion" gorm:"type:varchar(64);not null;comment:实例版本"`
	ClusterID      int64  `json:"clusterId" gorm:"type:int(11);not null;default:0;comment:关联表cluster"` // 指定关联外键，belongs to关系
	Model `json:",omitempty"`
}

type Cluster struct {
	ID            int64  `json:"id" gorm:"primaryKey;autoIncrement;column:id;comment:自增id"`
	Name          string `json:"name" gorm:"type:varchar(64);not null;default:;comment:集群名"`
	EnvCode       string `json:"envCode" gorm:"type:varchar(64);not null;default:;comment:环境code"`
	Model `json:",omitempty"`
}

// 联合查询 dbms_为表名前缀
type InstanceInfo struct {
		models.Instance
		Cluster models.Cluster
}
selectRes := new(InstanceInfo)
res := s.DB.Model(&models.Instance{}).Select("dbms_instance.*,dbms_cluster.*").Joins("Cluster").Where("dbms_instance.cluster_id = 1").Find(selectRes)
if res.Error != nil {
	return ""
}
fmt.Println(selectRes)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    常用关联gorm语句示例

    关联关键字：joins、preload

    • joins支持一对一、多对一，生成一条查询sql进行查询
    • preload都支持，分多次查询sql查询，并对结果聚合

    对于有where条件情况下的注意事项：

    • where中条件有几种方式

      • string，类似where(“id = ?”,id)或者where("name = @name or name2=@name ",map[string]interface{}{name:“xiaozhuang”})
      • struct，类似where(&Instance{Name:“xiaozhuang”})
      • map,类似where(&map[string]interface{}{“name”: “jinzhu”, “age”: 20})

      推荐使用struct，只有struct可以忽略零值，节省很多判断逻辑

    • 如果where条件中包含的是model/table或者接受结果的模型的表，则无论一对一、多对一、一对多都可以直接使用。

    • 一对多中，如果where条件中包含的是关联的表的条件，则需要preload+join结合使用进行查询 且joins中要手写一层关联关系，否则（只用joins手写关联关系）返回的结果只是进行了筛选，不会赋值到结果结构体中

      todo 例子

    示例：

    1. 一对一、多对一
    type Cluster struct {
	ID            int64                 `json:"id" gorm:"primaryKey;autoIncrement;column:id;comment:自增id"`
	Name          string                `json:"name" gorm:"type:varchar(64);not null;default:'';comment:集群名"`
	EnvCode       string                `json:"envCode" gorm:"type:varchar(64);not null;default:'';comment:环境code"`
	MasterVipHost string                `json:"masterVipHost" gorm:"type:varchar(128);not null;default:'';comment:集群读写vip地址"`
	Instances     []Instance            `json:"instance,omitempty"`
	Deleted       soft_delete.DeletedAt `json:"-" gorm:"index:idx_deleted"`
	Model         `json:",omitempty"`
}

type Instance struct {
	ID              int64                 `json:"id" gorm:"primaryKey;autoIncrement;column:id;comment:自增id"`
	Name            string                `json:"name" gorm:"type:varchar(64);not null;default:'';comment:集群名"`
	ManagePassword  string                `json:"managePassword" gorm:"type:varchar(256);not null;default:'';comment:管理密码"`
	ClusterID       int64                 `json:"clusterId" gorm:"type:int(11);not null;default:0;comment:关联表cluster"`
	ClusterRole     int                   `json:"clusterRole" gorm:"type:tinyint(4);not null;default:0;comment:在集群中的角色 3:主库 4:从库 5 离线库"`
	Cluster         Cluster               `json:"cluster,omitempty"`
	Deleted         soft_delete.DeletedAt `json:"-" gorm:"index:idx_deleted"`
	Model           `json:",omitempty"`
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    正确示例 where筛选条件只有查询的表有，或者没有条件，此时用joins和preload都可以实现

    var list []*models.Instancs
if err := s.DB.Joins("Cluster").Where(&models.Instance{InstanceType: 3}).Find(&list).Error; err != nil {
		return nil, 0, errors.Annotate(err, "查询数据库失败")
}
// select中内容进行截取，方便阅读
 SELECT `dbms_instance`.`id`,`dbms_instance`.`name`,`Cluster`.`id` AS `Cluster__id`, FROM `dbms_instance` LEFT JOIN `dbms_cluster` `Cluster` ON `dbms_instance`.`cluster_id` = `Cluster`.`id` AND `Cluster`.`deleted` = 0 WHERE `dbms_instance`.`instance_type` = 3 AND `dbms_instance`.`deleted` = 0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    错误示例 两个表都有where筛选条件条件

    可以发现语句只进行了条件筛选，并没有select出结果

     var list []*models.Instance
if err := s.DB.Joins("left join dbms_cluster t1 on t1.id = dbms_instance.cluster_id").
	  Where("t1.env_code = ?","hbos-test").
	 	Where(&models.Instance{InstanceType: 3}).Find(&list).Error; err != nil {
		return nil, 0, errors.Annotate(err, "查询数据库失败")
}
// select中内容进行截取，方便阅读
SELECT `dbms_instance`.`id`,`dbms_instance`.`name`,`dbms_instance`.`instance_host`, FROM `dbms_cluster` left join dbms_instance t1 on t1.cluster_id = dbms_cluster.id WHERE t1.instance_type = 3 AND `dbms_cluster`.`env_code` = 'hbos-test' AND `dbms_cluster`.`deleted` = 0


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    正确示例 两个表都有where筛选条件条件

    可以发现，通过preload语句先进行一次查询，两次查询结果进行聚合可以得到我们想要的多对一的查询结果

     var list []*models.Instance
if err := s.DB.Preload("Cluster").
		Joins("left join dbms_cluster t1 on t1.id = dbms_instance.cluster_id").
	  Where("t1.env_code = ?","hbos-test").
	 	Where(&models.Instance{InstanceType: 3}).Find(&list).Error; err != nil {
		return nil, 0, errors.Annotate(err, "查询数据库失败")
}
// select中内容进行截取，方便阅读
SELECT * FROM `dbms_cluster` WHERE `dbms_cluster`.`id` = 6 AND `dbms_cluster`.`deleted` = 0

 SELECT `dbms_instance`.`id`,`dbms_instance`.`name`,`dbms_instance`.`instance_host` FROM `dbms_instance` left join dbms_cluster t1 on t1.id = dbms_instance.cluster_id WHERE t1.env_code = 'hbos-test' AND `dbms_instance`.`instance_type` = 3 AND `dbms_instance`.`deleted` = 0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    1. 一对多
    type Cluster struct {
	ID            int64                 `json:"id" gorm:"primaryKey;autoIncrement;column:id;comment:自增id"`
	Name          string                `json:"name" gorm:"type:varchar(64);not null;default:'';comment:集群名"`
	EnvCode       string                `json:"envCode" gorm:"type:varchar(64);not null;default:'';comment:环境code"`
	MasterVipHost string                `json:"masterVipHost" gorm:"type:varchar(128);not null;default:'';comment:集群读写vip地址"`
	Instances     []Instance            `json:"instance,omitempty"`
	Deleted       soft_delete.DeletedAt `json:"-" gorm:"index:idx_deleted"`
	Model         `json:",omitempty"`
}

type Instance struct {
	ID              int64                 `json:"id" gorm:"primaryKey;autoIncrement;column:id;comment:自增id"`
	Name            string                `json:"name" gorm:"type:varchar(64);not null;default:'';comment:集群名"`
	ManagePassword  string                `json:"managePassword" gorm:"type:varchar(256);not null;default:'';comment:管理密码"`
	ClusterID       int64                 `json:"clusterId" gorm:"type:int(11);not null;default:0;comment:关联表cluster"`
	ClusterRole     int                   `json:"clusterRole" gorm:"type:tinyint(4);not null;default:0;comment:在集群中的角色 3:主库 4:从库 5 离线库"`
	Cluster         Cluster               `json:"cluster,omitempty"`
	Deleted         soft_delete.DeletedAt `json:"-" gorm:"index:idx_deleted"`
	Model           `json:",omitempty"`
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    正确示例 where筛选条件只有查询的表有，或者没有条件，此时只能用preload，用joins会报错

    注意：一对多下preload中值要和结构体中嵌入的“Instances”一致

    	var clusterIns []models.Cluster
	if err := s.DB.Preload("Instances").Where(&models.Cluster{ClusterType: MASTER}).
		Find(&clusterIns).Error; err != nil {
		return nil, 0, errors.Annotate(err, "查询实例所在集群记录失败")
	}

//
SELECT * FROM `dbms_instance` WHERE `dbms_instance`.`cluster_id` = 6 AND `dbms_instance`.`deleted` = 0
SELECT * FROM `dbms_cluster` WHERE `dbms_cluster`.`id` = 6 AND `dbms_cluster`.`deleted` = 0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    错误示例 where筛选条件只有查询的表有，或者没有条件，此时只能用preload，用joins会报错

    可以发现sql语句出现问题

    	var clusterIns []models.Cluster
	if err := s.DB.Preload("Instances").Where(&models.Cluster{ClusterType: MASTER}).
		Find(&clusterIns).Error; err != nil {
		return nil, 0, errors.Annotate(err, "查询实例所在集群记录失败")
	}

//
SELECT `dbms_cluster`.`id`,`dbms_cluster`.`name`,`dbms_cluster`.`env_code` FROM `dbms_cluster` Instance WHERE `dbms_cluster`.`cluster_type` = 3 AND `dbms_cluster`.`deleted` = 0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    正确示例 两个表都有where筛选条件条件

    var clusterIns []models.Cluster
if err := s.DB.Preload("Instances").
		Joins("left join dbms_instance t1 on t1.cluster_id = dbms_cluster.id").
		Where("t1.instance_type = ?", 3).
		Where(&models.Cluster{ClusterType: 3}).
		Find(&clusterIns).Error; err != nil {
		return nil, 0, errors.Annotate(err, "查询实例所在集群记录失败")
	}

// 		
SELECT * FROM `dbms_instance` WHERE `dbms_instance`.`cluster_id` = 20 AND `dbms_instance`.`deleted` = 0
SELECT `dbms_cluster`.`id`,`dbms_cluster`.`name` FROM `dbms_cluster` left join dbms_instance t1 on t1.cluster_id = dbms_cluster.id WHERE t1.instance_type = 3 AND `dbms_cluster`.`cluster_type` = 3 AND `dbms_cluster`.`deleted` = 0


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    总结：

    • 一对一关系：可直接使用gorm原生的Joins/preload来查询关联表的数据。
    • 多对一关系：多表有where条件要手写一层关联关系，否则直接用preload/Joins
    • 一对多关系： 多表有where条件要手写一层关联关系，否则直接用preload。
    • 多对多关系：有一张关联表，所以需要手写两层关联关系。
  • 相关阅读:
    Vue的模板语法（下）
    处理医学时间序列中缺失数据的3种方法
    使用IDEA远程debug调试
    5 Dijkstra算法的设计--来源王英S同学
    R语言数据结构-----向量
    资源道具化
    客户端远程连接mysql服务问题记录
    FreeRTOS简单内核实现3 任务管理
    迈德威视工业相机 Linux驱动详细步骤
    JavaScript判断字符串是否为数字类型：Number.isInteger、isNaN、正则表达式比较
  • 原文地址：https://blog.csdn.net/qq_43490312/article/details/128018144