lxutils/lxDb/model.go

package lxDb

import (
	"fmt"
	"git.listensoft.net/tool/lxutils/lxUtil"
	"gorm.io/gorm"
	"reflect"
	"strings"
	"time"
)

type BaseModel struct {
	ID        uint      `json:"id" gorm:"primaryKey"` // old version is: gorm:"primary_key", TODO: 兼容旧的 有没有影响?
	CreatedAt time.Time `json:"createdAt"`
	UpdatedAt time.Time `json:"updatedAt"`
	CreatedBy uint      `json:"createdBy"`
	UpdatedBy uint      `json:"updatedBy"`
}

// BaseModel2 带操作人ID的BaseModel
type BaseModel2 struct {
	BaseModel
	CreatedBy uint `json:"createdBy"`
	UpdatedBy uint `json:"updatedBy"`
	//DeletedBy uint `json:"-"`
}

//func (m BaseModel)AsID (id uint) {
//	m.ID = id
//}

// NewModel 返回被 uid 填充的 model
//func NewModel(uid uint) Model {
//	return Model{CreatedBy: uid, UpdatedBy: uid}
//}

// Condition 查询条件
type Condition struct {
	Field    string      // 字段
	Operator string      // 操作类型
	Arg      interface{} // 值
}

// PaginationQuery 查询条件
// TODO: 考虑在包之外使用方法修改属性而不是直接操作属性
// TODO: 考虑要明确指定不分页查询, 否则就强制分页, 以免误查大量数据, 但是给非前端调用带来麻烦
// TODO: 要明确指定是不分页查询, 默认“下一页”不查询total. 考虑前端使用的友好性，及可配置化(config)
type PaginationQuery struct {
	Conditions    []Condition            `json:"-"`             // 查询条件
	OrderBy       string                 `json:"orderBy"`       // 排序 会被直接拼接到 sql 里 // TODO: 这个会保留吗, 支持特殊的自定义排序.
	Orders        []string               `json:"orders"`        // 排序 会被直接拼接到 sql 里 // TODO: 支持多条件排序 string 还是对象? 还未做
	Offset        int                    `json:"page"`          // 第几页
	Limit         int                    `json:"limit"`         // 一页几条数据 or size ? // TODO:
	NoTotal       bool                   `json:"noTotal"`       // 不需要记录条数, true: 不查询Total
	Total         int                    `json:"total"`         // 记录条数
	Summary       string                 `json:"summary"`       // 汇总字段,如: "inAmt,inCount"
	SummarySql    []string               `json:"-"`             // 汇总子sql, 如: ["SUM(int_amt) AS inAmt", "SUM(int_count) AS inCount"]
	SummaryResult map[string]interface{} `json:"summaryResult"` // 汇总结果
}

// Eq equal 等于
func (q *PaginationQuery) Eq(field string, arg interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "eq", Arg: arg})
}

// Ne not equal 不等于
func (q *PaginationQuery) Ne(field string, arg interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "ne", Arg: arg})
}

// Lt less than 小于
func (q *PaginationQuery) Lt(field string, arg interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "lt", Arg: arg})
}

// Le less equal 小于等于
func (q *PaginationQuery) Le(field string, arg interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "le", Arg: arg})
}

// Gt greater than 大于
func (q *PaginationQuery) Gt(field string, arg interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "gt", Arg: arg})
}

// Ge greater equal 大于等于
func (q *PaginationQuery) Ge(field string, arg interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "ge", Arg: arg})
}

// Like 包含 LIKE '%arg%'
func (q *PaginationQuery) Like(field string, arg interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "like", Arg: arg})
}

// Like2 自指定包含方式 LIKE 'arg'. arg是灵活的, 如: 前匹配abs%, 后匹配%abc, 通配符'_1_'
func (q *PaginationQuery) Like2(field string, value interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "like2", Arg: value})
}

func (q *PaginationQuery) NotLike(field string, value interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "notlike", Arg: value})
}

// In IN (...)
func (q *PaginationQuery) In(field string, arg interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "in", Arg: arg})
}

// Between BETWEEN v1 AND v2
func (q *PaginationQuery) Between(field string, v1, v2 interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "between", Arg: [2]interface{}{v1, v2}})
}

// Or OR
func (q *PaginationQuery) Or(field string, arg interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "or", Arg: arg})
}

// Ors Deprecated: replaced by And 自定义条件, 如: (length(field) = 4 OR length(field) = 6)
func (q *PaginationQuery) Ors(condition string) {
	q.And("(" + condition + ")")
}

// And 自定义条件, 如: (length(field) = 4 OR length(field) = 6)
func (q *PaginationQuery) And(condition string) {
	q.Conditions = append(q.Conditions, Condition{Field: condition, Operator: "and"})
}

// Group GROUP BY
func (q *PaginationQuery) Group(field string) {
	q.Conditions = append(q.Conditions, Condition{Field: field, Operator: "group"})
}

// Join JOIN table_name ON
func (q *PaginationQuery) Join(joinSql string, args ...interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: joinSql, Operator: "join", Arg: args})
}

// Select 要查询的字段. 可以将字段都写在第一个参数, 也可以作为多个参数传递. ("aa, bb, cc") == ("aa", "bb", "cc")
func (q *PaginationQuery) Select(query string, args ...interface{}) {
	q.Conditions = append(q.Conditions, Condition{Field: query, Operator: "select", Arg: args})
}

// Build 构造查询条件
func (q *PaginationQuery) Build(tx *gorm.DB) *gorm.DB {
	for _, con := range q.Conditions {
		switch con.Operator {
		case "eq":
			field := fmt.Sprintf("%s = ?", con.Field)
			tx = tx.Where(field, con.Arg)
		case "ne":
			field := fmt.Sprintf("%s <> ?", con.Field)
			tx = tx.Where(field, con.Arg)
		case "lt":
			field := fmt.Sprintf("%s < ?", con.Field)
			tx = tx.Where(field, con.Arg)
		case "le":
			field := fmt.Sprintf("%s <= ?", con.Field)
			tx = tx.Where(field, con.Arg)
		case "gt":
			field := fmt.Sprintf("%s > ?", con.Field)
			tx = tx.Where(field, con.Arg)
		case "ge":
			field := fmt.Sprintf("%s >= ?", con.Field)
			tx = tx.Where(field, con.Arg)
		case "like":
			field := fmt.Sprintf("%s LIKE ?", con.Field) // field LIKE '%arg%'
			tx = tx.Where(field, fmt.Sprintf("%%%v%%", con.Arg))
		case "like2":
			field := fmt.Sprintf("%s LIKE ?", con.Field) // field LIKE 'arg', support: 'abc%', '%abc', '_1_'
			tx = tx.Where(field, con.Arg)
		case "notlike":
			field := fmt.Sprintf("%s NOT LIKE ?", con.Field)
			tx = tx.Where(field, fmt.Sprintf("%%%v%%", con.Arg))
		case "in":
			field := fmt.Sprintf("%s IN (?)", con.Field)
			tx = tx.Where(field, con.Arg)
		case "between":
			field := fmt.Sprintf("%s BETWEEN ? AND ?", con.Field)
			value1, value2, ok := split(con.Arg)
			if ok {
				tx = tx.Where(field, value1, value2)
			}
		case "or":
			field := fmt.Sprintf("%s = ?", con.Field)
			tx = tx.Or(field, con.Arg)
		case "and":
			tx = tx.Where(con.Field) // 自定义条件, 如: (length(field) = 4 OR length(field) = 6)
		case "group":
			tx = tx.Group(con.Field)
		case "join":
			//args := cast.ToStringSlice(con.Arg) // not work, cast.ToSlice not work too
			tx = tx.Joins(con.Field, lxUtil.ToSlice1(con.Arg)...)
		case "select":
			tx = tx.Select(con.Field, lxUtil.ToSlice1(con.Arg)...)
		}
	}
	return tx
}

// BuildRawWhere 构造原生SQL的查询条件
func (q *PaginationQuery) BuildRawWhere() (where string, args []interface{}) {
	var sb strings.Builder
	for _, con := range q.Conditions {
		switch con.Operator {
		case "eq":
			sb.WriteString(fmt.Sprintf(" AND %s = ?", con.Field))
			args = append(args, con.Arg)
		case "ne":
			sb.WriteString(fmt.Sprintf(" AND %s <> ?", con.Field))
			args = append(args, con.Arg)
		case "lt":
			sb.WriteString(fmt.Sprintf(" AND %s < ?", con.Field))
			args = append(args, con.Arg)
		case "le":
			sb.WriteString(fmt.Sprintf(" AND %s <= ?", con.Field))
			args = append(args, con.Arg)
		case "gt":
			sb.WriteString(fmt.Sprintf(" AND %s > ?", con.Field))
			args = append(args, con.Arg)
		case "ge":
			sb.WriteString(fmt.Sprintf(" AND %s >= ?", con.Field))
			args = append(args, con.Arg)
		case "like":
			sb.WriteString(fmt.Sprintf(" AND %s LIKE ?", con.Field)) // field LIKE '%arg%'
			args = append(args, fmt.Sprintf("%%%v%%", con.Arg))
		case "like2":
			sb.WriteString(fmt.Sprintf(" AND %s LIKE ?", con.Field)) // field LIKE 'arg', support: 'abc%', '%abc', '_1_'
			args = append(args, con.Arg)
		case "notlike":
			sb.WriteString(fmt.Sprintf(" AND %s NOT LIKE ?", con.Field))
			args = append(args, fmt.Sprintf("%%%v%%", con.Arg))
		case "in":
			sb.WriteString(fmt.Sprintf(" AND %s IN (?)", con.Field))
			args = append(args, con.Arg)
		case "between":
			value1, value2, ok := split(con.Arg)
			if ok {
				sb.WriteString(fmt.Sprintf(" AND %s BETWEEN ? AND ?", con.Field))
				args = append(args, value1, value2)
			}
		case "or":
			sb.WriteString(fmt.Sprintf(" OR %s = ?", con.Field))
			args = append(args, con.Arg)
		case "and":
			sb.WriteString(fmt.Sprintf(" AND %s", con.Field)) // 自定义条件, 如: (length(field) = 4 OR length(field) = 6)
		case "group":
			sb.WriteString(fmt.Sprintf(" GROUP BY %s", con.Field))

			//case "join":
			//	if con.Arg == nil {
			//		tx = tx.Joins(con.Field)
			//	} else {
			//		tx = tx.Joins(con.Field, con.Arg)
			//	}
			//case "select":
			//	if con.Arg == nil {
			//		tx = tx.Select(con.Field)
			//	} else {
			//		tx = tx.Select(con.Field, con.Arg)
			//	}
		}
	}
	where = sb.String()
	if args == nil {
		args = make([]interface{}, 0)
	}
	return
}

// 参数拆分 // TODO: 不知道有没有更好的实现方式
// TODO: 加异常处理, 如应该两个值只传了一个值
//func split(value interface{}) (interface{}, interface{}) {
//	values := value.([2]interface{})
//	return values[0], values[1]
//}

// 参数拆分
func split(value interface{}) (v1 interface{}, v2 interface{}, ok bool) {
	rv := reflect.ValueOf(value)
	// 取值
	for rv.Kind() == reflect.Ptr {
		rv = rv.Elem()
	}
	// 校验是否为长度为 2 的数组或切片
	if (rv.Kind() != reflect.Slice && rv.Kind() != reflect.Array) || rv.Len() != 2 {
		return
	}
	return rv.Index(0).Interface(), rv.Index(1).Interface(), true
}