acmestack / gorm-plus Goto Github PK

需求案例场景：子查询、自定义sql片段
伪代码：

	var expectSql = "SELECT * FROM `Users` WHERE username = 'afumu' OR age = 20"
	sessionDb := checkSelectSql(t, expectSql)
	query, u := gplus.NewQuery[User]()
	query.Eq(&u.Username, "afumu").Or().Eq(&u.Age, 20).Apply("AND id IN (SELECT user_id FROM app_user)")
	gplus.SelectList[User](query, gplus.Db(sessionDb))

We need to connect to multiple databases in our project, but gplus's gorm as a global variable.
can gplus support multi gorm??

Enviornment (please complete the following information):

• OS: [Linux: uname -a]
• envcd version [e.g. v0.0.1]

Describe the bug
A clear and concise description of what the bug is.

To Reproduce
Steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

定义如下结构体

type Base struct {
Id int64
CreatedAt time.Time json:"createdAt"
UpdatedAt time.Time json:"updatedAt"
}

type User struct {
Base
Username string gorm:"column:username"
Password string
Address string
Age int
Phone string
Score int
Dept string
}

在更新的时候，会直接使用base作为参数名

Expected behavior
A clear and concise description of what you expected to happen.

在model中，create_time和modify_time字段期望在新增/修改记录时，由数据库自动变更。
之前使用gorm的写法是：

data := &Students{
		Class: 2,
		Name:  "Mono",
		Age:   29,
}

db.Omit("create_time", "modify_time").Create(data)

切换到gorm-plus后，目前是这么实现的：

data := &Students{
		Class: 1,
		Name:  "Speci",
		Age:   12,
}

gplus.Insert[Students](data)

这样会报 Error 1292 (22007): Incorrect datetime value: '0000-00-00' for column 'create_time' at row 1
想问下目前gorm-plus有相关的解决方案么？

bug案例复现：

func TestSelectListOr(t *testing.T) {
	var expectSql = "SELECT * FROM `Users` WHERE username = 'afumu' OR age = 20"
	sessionDb := checkSelectSql(t, expectSql)
	query, u := gplus.NewQuery[User]()
	query.Eq(&u.Username, "afumu").Or().Eq(&u.Age, 20).Or(func(q *gplus.QueryCond[User]) {

	})
	gplus.SelectList[User](query, gplus.Db(sessionDb))
}

期望得到的结果：

SELECT * FROM `Users` WHERE username = 'afumu' OR age = 20

实际得到的结果：

SELECT * FROM `Users` WHERE username = 'afumu' OR age OR () = 20

导致bug出现bug的函数：

func buildSqlAndArgs[T any](expressions []any, sqlBuilder *strings.Builder, queryArgs []any) []any {
	for _, v := range expressions {
		// 判断是否是columnValue类型
		switch segment := v.(type) {
		case *columnPointer:
			sqlBuilder.WriteString(segment.getSqlSegment() + " ")
		case *sqlKeyword:
			sqlBuilder.WriteString(segment.getSqlSegment() + " ")
		case *columnValue:
			if segment.value == constants.And {
				sqlBuilder.WriteString(segment.value.(string) + " ")
				continue
			}
			if segment.value != "" {
				sqlBuilder.WriteString("? ")
				queryArgs = append(queryArgs, segment.value)
			}
		case *QueryCond[T]:
			sqlBuilder.WriteString(constants.LeftBracket + " ")
			// 递归处理条件
			queryArgs = buildSqlAndArgs[T](segment.queryExpressions, sqlBuilder, queryArgs)
			sqlBuilder.WriteString(constants.RightBracket + " ")
		}
	}
	return queryArgs
}

bug fix方案：

func buildSqlAndArgs[T any](expressions []any, sqlBuilder *strings.Builder, queryArgs []any) []any {
	for _, v := range expressions {
		// 判断是否是columnValue类型
		switch segment := v.(type) {
		case *columnPointer:
			sqlBuilder.WriteString(segment.getSqlSegment() + " ")
		case *sqlKeyword:
			sqlBuilder.WriteString(segment.getSqlSegment() + " ")
		case *columnValue:
			if segment.value == constants.And {
				sqlBuilder.WriteString(segment.value.(string) + " ")
				continue
			}
			if segment.value != "" {
				sqlBuilder.WriteString("? ")
				queryArgs = append(queryArgs, segment.value)
			}
		case *QueryCond[T]:
			// 当子条件不存在查询表达式时，无需进行递归处理
			if len(segment.queryExpressions) == 0 {
				continue
			}
			sqlBuilder.WriteString(constants.LeftBracket + " ")
			// 递归处理条件
			queryArgs = buildSqlAndArgs[T](segment.queryExpressions, sqlBuilder, queryArgs)
			sqlBuilder.WriteString(constants.RightBracket + " ")
		}
	}
	return queryArgs
}

type Config struct {
Id string
Name string
}

场景如下：通过租户或者ID首位分表（如user_1、user_2、user_3等），这种场景下只有前端的请求到来的时候，才知道应该具体去查询哪一张表，我在代码里没有看到关于表名的设置，目前我用的是GORM原生的Scopes方法去动态加载表名，可以为这个需求添加支持吗？

1

gorm对count的执行如果包含distinct 或者列别名会执行报错在mysql，我找了相应的issue ：go-gorm/gorm#5821

我的用法：

报错的地方：
func SelectCount[T any](q *QueryCond[T], opts ...OptionFunc) (int64, *gorm.DB) {
var count int64
resultDb := buildCondition(q, opts...)
resultDb.Count(&count) //这里执行会生成sql不正确
return count, resultDb
}

sql生成比方说是生成这样的:SELECT COUNT(Id as id) FROM t_test 这种语法在mysql执行是报错的，
因为gorm里对count逻辑：

所以在不用到类似distinct什么情况下，所有sql一律生成 select count(*) from t_test ，不考虑复杂情况
所以我的解决方案我是想在这边执行Count的时候加个resultDb.Statement.Selects = nil
// SelectCount 根据条件查询记录数量
func SelectCount[T any](q *QueryCond[T], opts ...OptionFunc) (int64, *gorm.DB) {
var count int64
resultDb := buildCondition(q, opts...)
//fix 查询分页数量有列Select只有一个则生成sql不对问题
resultDb.Statement.Selects = nil
resultDb.Count(&count)
return count, resultDb
}

这种感觉也是组件gorm的问题~~~~对各个驱动并没有完全匹配
主要我用的使用列别名转了，如果单个字段，列别名不转的话没有问题，在gorm执行count的时候select count(fieldName) from 表 这样sql执行是正确的。不过总体上gorm的count兼容问题？

我们有一些字段，在数据插入前需要针对他们进行json序列化、加密等操作，后续是否会有相关的typeHandler进行支持呢？

selectOne和selectId 的搜索时建议使用First

源码里面使用的limit+find。使用find后，如果查不到数据，数据接收对象被实例化了一个空的对象；并且没有gorm.ErrRecordNotFound的错误了。

我这边用压测工具试了下。发现时间一长就出现
2024/02/03 12:14:52 C:/Users/lg/go/pkg/mod/github.com/acmestack/[email protected]/gplus/dao.go:172 dial tcp 127.0.0.1:3306: connectex: Only one usage of each socket address (protocol/network address/port) is normally permitted.
连接不够了。这个连接池的释放管理是不是有问题。下面查询语句一直都慢查询了。感觉似乎就是有空余连接没有释放卡住了。

还有另外一个是
2024/02/03 12:14:52 C:/Users/lg/go/pkg/mod/github.com/acmestack/[email protected]/gplus/dao.go:172 SLOW SQL >= 200ms
[7062.620ms] [rows:3] SELECT * FROM configs 这个日志计时的监控有没有办法关掉。

1. This query tool automatically provides the query function

getColumnNameMap

我想使用gormplus进行关联表查询，但是似乎未找到该功能，后续还有计划添加和维护么

在一些不需要跳页的场景下使用，相比于offset具有更好的性能。
伪代码如下：

type Comparable interface {
	~int | ~int8 | ~int16 | ~int32 | ~int64 | ~uint | ~uint8 | ~uint16 | ~uint32 | ~uint64 | ~float32 | ~float64 | time.Time
}

type StreamingPage[T any, V Comparable] struct {
	Column     string `json:"column"`     // 进行分页的列字段
	StartValue T      `json:"startValue"` // 分页起始值
	Limit      int    `json:"limit"`      // 页大小
	Forward    bool   `json:"forward"`    // 上下页翻页标识
	Total      int64  `json:"total"`      // 总记录数
	Records    []*T   `json:"records"`    // 查询记录
	RecordsMap []T    `json:"recordsMap"` // 查询记录Map
}

// StreamingPaginate 流式分页，根据自增ID、雪花ID、时间等数值类型或者时间类型分页
// Tips: 相比于 offset 分页性能更好，走的是 range，缺点是没办法跳页查询
func StreamingPaginate[T any, V Comparable](p *StreamingPage[T, V]) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		// 下一页
		if p.Forward {
			return db.Where(fmt.Sprintf("%v > ?", p.Column), p.StartValue).Limit(p.Limit)
		}
		// 上一页
		return db.Where(fmt.Sprintf("%v < ?", p.Column), p.StartValue).Order(fmt.Sprintf("%v DESC", p.Column)).Limit(p.Limit)
	}
}

The structure embeds all the query methods of this top-level structure combination

Enviornment (please complete the following information):

• OS: [Linux: uname -a]
• envcd version [e.g. v0.0.1]

Describe the bug
A clear and concise description of what the bug is.

func page(c *gin.Context) {
	query := &QueryPage{}
	_ = c.ShouldBindQuery(query)
	page := gplus.NewPage[User](query.Current, query.PageSize)
	page, _ = gplus.SelectPage(page, gplus.BuildQuery[User](c.Request.URL.Query()))
	c.JSON(200, gin.H{
		"code": 0,
		"data": page,
	})
}
user.GET("/page", page)

返回

{
    "code": 0,
    "data": {
        "Current": 0,
        "Size": 0,
        "Total": 5,
        "Records": [
            {
                "id": 407,
                "username": "张三",
                "password": "password1",
                "address": "地址1",
                "age": 25,
                "phone": "12345678901",
                "score": 80,
                "dept": "部门1",
                "createdAt": "2023-08-12T07:03:05+08:00",
                "updatedAt": "2023-08-12T07:03:05+08:00"
            }
        ],
        "RecordsMap": null
    }
}

page的默认字段怎么改成小驼峰？

To Reproduce
Steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior
A clear and concise description of what you expected to happen.

什么时候才有根据数据库生成实体类 CRUD功能 ，还有分页功能和事务什么时候有呀 期待你的更新，忘回复

您好，复刻了一份您的代码，用于自己自定义的修改。
修改了结构和表名
保留了原来出处

如果获取 db设置 context再次设置到 dao，这会有问题
gplus.Init(dao.Db().WithContext(ctx))

接口操作
第一次不设置context save操作
第二次设置 context get操作

再次调用接口结果context canceled

tagSetting := schema.ParseTagSetting(field.Tag.Get("gorm"), ";")
isPrimaryKey := utils.CheckTruth(tagSetting["PRIMARYKEY"], tagSetting["PRIMARY_KEY"])

sqlite,我是用 type 标签去生成数据库表结构，工具查看数据库结构没有问题，上面的判断主键是否需要做个调整
Id int json:"id" gorm:"type:integer not null primary key autoincrement;"

2023/06/19 10:40:49 D:/test/gorm-plus/gplus/dao.go:172
[0.000ms] [rows:0] SELECT * FROM Users ORDER BY username DESC,age ASC
--- FAIL: TestSelectListQueryModel (0.00s)
panic: interface conversion: interface {} is *tests.UserVo, not *tests.UserVo (types from different scopes) [recovered]
panic: interface conversion: interface {} is *tests.UserVo, not *tests.UserVo (types from different scopes)

goroutine 512 [running]:
testing.tRunner.func1.2({0x4f4280, 0xc00034f8f0})
D:/Program Files/golang/go1.20.3/src/testing/testing.go:1526 +0x24e
D:/Program Files/golang/go1.20.3/src/testing/testing.go:1576 +0x10b
created by testing.(*T).Run
D:/Program Files/golang/go1.20.3/src/testing/testing.go:1629 +0x3ea
exit status 2
FAIL github.com/acmestack/gorm-plus/tests 8.290s
PS D:\test\gorm-plus\tests>

I have a big table, and do not want to get the total using selectPage function .
Because if I want to get the total, I can use the function SelectCount.
So please add a flag to not output the total in the page functions, thanks.

新特性里面增加了字段忽略和条件查询的功能，里面用到的字段反射都是基于一层结构体的，建议改为下面的模式适配测试代码

type Base struct {
ID int64
CreatedAt time.Time
UpdatedAt time.Time
}

type User struct {
Base
Username string
Password string
Address string
Age int
Phone string
Score int
Dept string
}

func (User) TableName() string {
return "Users"
}

具体哪里改我还没看，有改法了我再提交申请

can ColumnNameMap cached for same Entity? Do not call buildColumnNameMap every time.

Background & X Problem
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Proposal and Expectation
A clear and concise description of what you want to happen.

Solutions & Y Problems
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

伪代码如下：

        // 变量 user 为入参
	query, u := gplus.NewQuery[User]()
	query.Eq(&u.Password, "12345678").Or(func(q *gplus.QueryCond[User]) {
		q.EqCond(user.Id > 0,&u.Id, user.Id).
			Or().EqCond(len(user.Account) > 0, &u.Account, user.Account).
			Or().EqCond(len(user.Email) > 0, &u.Email, user.Email).
			Or().EqCond(len(user.Phone) > 0, &u.Phone, user.Phone)
	}).OrderByAsc(&u.Id)

当条件为false时，EqCond的条件不会加入到sql中，但是因为前面用了一个Or()作为条件关系，会出现这种情况：

SELECT * FROM `user` WHERE password = '12345678' OR ( id = 0 OR account = 'admin' OR )  ORDER BY id ASC LIMIT 1

应该所有的*Cond条件函数，在使用Or()或者And()之后都会出现这种情况，需要做特殊的适配，AndCond()和OrCond()没办法用在这种条件分组的情况

Gorm only support use map to update column to a zero(0，false，nil etc.) value. However now gplus can't support in this version. Will you support in the future?

go version 1.19.1

run go mod tidy:

go: finding module for package github.com/acmestack/gorm-plus
github.com/acmestack/gorm-plus: module github.com/acmestack/gorm-plus@latest found (v0.0.15), but does not contain package github.com/acmestack/gorm-plus

下面这样应该也行
// 开启事务
tx := gplus.Begin()

// 使用defer，实现遇到错误时回滚
defer tx.Rollback()

// 新增，传入tx
student := Student{Name: "zhangsan", Age: 18, Email: "[email protected]", Birthday: time.Now()}
result := gplus.Insert(&student, tx)
...

// 更新，传入tx
query, model := gplus.NewQueryStudent
query.Eq(&model.Name, "zhangsan").Set(&model.Age, 30)
resultDb := gplus.Update[Student](query, tx)
...

// 查询，如果需要在事务中查询，则也需要传入tx
resultStudent, db := gplus.SelectById[Student](student.ID, tx)
...

// 提交事务，否则数据库数据不更改
tx.Commit()

按照这个方式传入tx，dao的方法需要一个OptionFunc
Cannot use 'tx' (type *gorm.DB) as the type OptionFunc

Background & X Problem
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Proposal and Expectation
A clear and concise description of what you want to happen.

Solutions & Y Problems
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

for example：

When I query with the following statement:

The results can still be found: