- 表结构
--订单表
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
...