gorm 关联查询介绍 (关联中关联）

• 表结构

--订单表
CREATE TABLE `order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT '0' COMMENT '用户id',
  `no` varchar(50) NOT NULL DEFAULT '' COMMENT '订单号',
  ...
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_no` (`no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

-- 投保人表
CREATE TABLE `order_applicant` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL DEFAULT '0' COMMENT '订单id',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '姓名',
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_order_id` (`order_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='投保人信息';

-- 理赔表
CREATE TABLE `order_compensation` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `no` varchar(50) NOT NULL DEFAULT '' COMMENT '订单号',
  `order_id` int(11) NOT NULL DEFAULT '0' COMMENT '订单ID',
  ...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COMMENT='理赔';

• 设置model

type Order struct {
	models.Model
	UserId              int       `gorm:"column:user_id" json:"user_id" description:"用户id"`
	No                  string    `gorm:"column:no" json:"no" description:"订单号"`
	...
	// 投保人信息
	Applicant OrderApplicant `json:"applicant"`
}
type OrderApplicant struct {
	models.Model
	OrderId            int    `gorm:"column:order_id" json:"order_id" description:"订单id"`
	Name               string `gorm:"column:name" json:"name" description:"姓名"`
	...
}

type OrderCompensation struct {
	models.Model
	No             string  `gorm:"column:no" json:"no" description:"订单号"`
	OrderId        int     `gorm:"column:order_id" json:"order_id" description:"订单ID"`
	...
	
	// 订单信息
	Order Order `gorm:"FOREIGNKEY:order_id" json:"order"`
}

场景1：订单模型中找出投保人信息

db.model(&Order).
Preload("OrderApplicant", fun(db *gorm.DB) *gorm.DB {
	return db.select("").where("")...
}...

场景2：理赔模型中找出订单及投保人

db.model(&OrderCompensation{}).
Preload("Order", fun(db *gorm.DB) *gorm.DB {
	return db.select()...
}.
Preload("Order.Applicant", fun(db *gorm.DB) *gorm.DB {
	// 此处使用select时一定要注意必须要包含 "order_id"这个字段，不然找到后无法附值到对应的order中
	return db.select()....
}

• 自己封装的数据传递

// TableSearch 搜索数据
type TableSearch struct {
	WhereMaps map[string]interface{} // 查找where
	SelectColumns []string // 查询字段
	OrderBy string // 排序
	Offset int64
	Limit int64 // 限制条数
	Unscoped bool // true 查询已删除的
	RelateTables []RelateTable // 关联表信息
}

type RelateTable struct {
	TableField string // 表字段
	WhereMaps map[string]interface{} // 查找where
	SelectColumns []string // 查询字段
	OrderBy string // 排序
	Offset int64
	Limit int64 // 限制条数
	Unscoped bool // true 查询已删除的
}

// 执行函数

/ SearchBuild 组成新的结构
func SearchBuild(search TableSearch) (*gorm.DB, error) {
	if !search.Unscoped {
		//  查找其他
		if search.WhereMaps == nil {
			search.WhereMaps = map[string]interface{}{
				"deleted_on": time.Time{},
			}
		} else {
			search.WhereMaps["deleted_on"] = time.Time{}
		}
	}

	modelsDb := Db
	if len(search.SelectColumns) > 0 {
		modelsDb = modelsDb.Select(search.SelectColumns)
	}
	if len(search.RelateTables) > 0 {
		modelsDb = RelateTableBuild(modelsDb, search.RelateTables)
	}

	if search.WhereMaps != nil {
		whereSql, args, err := AutoBuildWhere(search.WhereMaps)
		if err != nil {
			return nil, err
		}
		modelsDb = modelsDb.Where(whereSql, args...)
	}

	return modelsDb.Offset(search.Offset).Limit(search.Limit).
		Order(search.OrderBy), nil
}

// RelateTableBuild 生成关联表关系
func RelateTableBuild(DB *gorm.DB, rTables []RelateTable) *gorm.DB {
	for _, v := range rTables {
		if !v.Unscoped {
			if v.WhereMaps == nil {
				v.WhereMaps = map[string]interface{}{
					"deleted_on": time.Time{},
				}
			} else {
				v.WhereMaps["deleted_on"] = time.Time{}
			}
		}
		DB = DB.Preload(v.TableField, relateTableBuildFuc(v))
	}

	return DB
}

func relateTableBuildFuc(search RelateTable) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if len(search.SelectColumns) > 0 {
			db = db.Select(search.SelectColumns)
		}
		if len(search.WhereMaps) > 0 {
			whereSql, args, err := AutoBuildWhere(search.WhereMaps)
			if err != nil {
				panic(err)
			}
			db = db.Where(whereSql, args...)
		}
		if search.Limit > 0 {
			if search.Offset > 0 {
				db = db.Offset(search.Offset)
			}
			db = db.Limit(search.Limit)
		}
		if search.OrderBy != "" {
			db = db.Order(search.OrderBy)
		}

		return db
	}
}

• 如何使用

search := TableSearch{
	WhereMaps: map[string]interface{}{
				"id": r.Id,
			},
	SelectColumns: nil,
	OrderBy:       "",
	Offset:        0,
	Limit:         0,
	Unscoped:      false, 
	RelateTables: []models.RelateTable{
			{TableField: "Order", SelectColumns: []string{"id", "no"}},
			{TableField: "Order.Applicant", SelectColumns: []string{"id", "order_id"}}, // order_id 必须存在
		},
}


db, err := models.SearchBuild(search)
if err != nil {
	return nil, err
}

err = db.Find(&orderCompensations).Error
...