feat: entgo

entgo/query/filter.go

@@ -3,14 +3,13 @@ package entgo
 import (
 	"encoding/json"
 	"fmt"
+	"github.com/tx7do/go-utils/stringcase"
 	"strings"
 
 	"entgo.io/ent/dialect"
 	"entgo.io/ent/dialect/sql"
 
 	"github.com/go-kratos/kratos/v2/encoding"
-
-	"github.com/tx7do/go-utils/stringcase"
 )
 
 type FilterOp int
@@ -63,16 +62,6 @@ var ops = [...]string{
 	FilterSearch:                "search",
 }
 
-func hasOperations(str string) bool {
-	str = strings.ToLower(str)
-	for _, item := range ops {
-		if str == item {
-			return true
-		}
-	}
-	return false
-}
-
 type DatePart int
 
 const (
@@ -109,6 +98,38 @@ var dateParts = [...]string{
 	DatePartMicrosecond: "microsecond",
 }
 
+const (
+	QueryDelimiter     = "__" // 分隔符
+	JsonFieldDelimiter = "."  // JSONB字段分隔符
+)
+
+// splitQueryKey 分割查询键
+func splitQueryKey(key string) []string {
+	return strings.Split(key, QueryDelimiter)
+}
+
+// splitJsonFieldKey 分割JSON字段键
+func splitJsonFieldKey(key string) []string {
+	return strings.Split(key, JsonFieldDelimiter)
+}
+
+// isJsonFieldKey 是否为JSON字段键
+func isJsonFieldKey(key string) bool {
+	return strings.Contains(key, JsonFieldDelimiter)
+}
+
+// hasOperations 是否有操作
+func hasOperations(str string) bool {
+	str = strings.ToLower(str)
+	for _, item := range ops {
+		if str == item {
+			return true
+		}
+	}
+	return false
+}
+
+// hasDatePart 是否有日期部分
 func hasDatePart(str string) bool {
 	str = strings.ToLower(str)
 	for _, item := range dateParts {
@@ -119,6 +140,32 @@ func hasDatePart(str string) bool {
 	return false
 }
 
+// BuildFilterSelector 构建过滤选择器
+func BuildFilterSelector(andFilterJsonString, orFilterJsonString string) (error, []func(s *sql.Selector)) {
+	var err error
+	var queryConditions []func(s *sql.Selector)
+
+	var andSelector func(s *sql.Selector)
+	err, andSelector = QueryCommandToWhereConditions(andFilterJsonString, false)
+	if err != nil {
+		return err, nil
+	}
+	if andSelector != nil {
+		queryConditions = append(queryConditions, andSelector)
+	}
+
+	var orSelector func(s *sql.Selector)
+	err, orSelector = QueryCommandToWhereConditions(orFilterJsonString, true)
+	if err != nil {
+		return err, nil
+	}
+	if orSelector != nil {
+		queryConditions = append(queryConditions, orSelector)
+	}
+
+	return nil, queryConditions
+}
+
 // QueryCommandToWhereConditions 查询命令转换为选择条件
 func QueryCommandToWhereConditions(strJson string, isOr bool) (error, func(s *sql.Selector)) {
 	if len(strJson) == 0 {
@@ -150,14 +197,13 @@ func QueryCommandToWhereConditions(strJson string, isOr bool) (error, func(s *sq
 	}
 }
 
+// processQueryMap 处理查询映射表
 func processQueryMap(s *sql.Selector, queryMap map[string]string) []*sql.Predicate {
 	var ps []*sql.Predicate
 	for k, v := range queryMap {
-		key := stringcase.ToSnakeCase(k)
+		keys := splitQueryKey(k)
 
-		keys := strings.Split(key, "__")
-
-		if cond := oneFieldFilter(s, keys, v); cond != nil {
+		if cond := makeFieldFilter(s, keys, v); cond != nil {
 			ps = append(ps, cond)
 		}
 	}
@@ -165,32 +211,8 @@ func processQueryMap(s *sql.Selector, queryMap map[string]string) []*sql.Predica
 	return ps
 }
 
-func BuildFilterSelector(andFilterJsonString, orFilterJsonString string) (error, []func(s *sql.Selector)) {
-	var err error
-	var queryConditions []func(s *sql.Selector)
-
-	var andSelector func(s *sql.Selector)
-	err, andSelector = QueryCommandToWhereConditions(andFilterJsonString, false)
-	if err != nil {
-		return err, nil
-	}
-	if andSelector != nil {
-		queryConditions = append(queryConditions, andSelector)
-	}
-
-	var orSelector func(s *sql.Selector)
-	err, orSelector = QueryCommandToWhereConditions(orFilterJsonString, true)
-	if err != nil {
-		return err, nil
-	}
-	if orSelector != nil {
-		queryConditions = append(queryConditions, orSelector)
-	}
-
-	return nil, queryConditions
-}
-
-func oneFieldFilter(s *sql.Selector, keys []string, value string) *sql.Predicate {
+// makeFieldFilter 构建一个字段过滤器
+func makeFieldFilter(s *sql.Selector, keys []string, value string) *sql.Predicate {
 	if len(keys) == 0 {
 		return nil
 	}
@@ -207,6 +229,21 @@ func oneFieldFilter(s *sql.Selector, keys []string, value string) *sql.Predicate
 
 	switch len(keys) {
 	case 1:
+		if isJsonFieldKey(field) {
+			jsonFields := splitJsonFieldKey(field)
+			if len(jsonFields) != 2 {
+				field = stringcase.ToSnakeCase(field)
+				return filterEqual(s, p, field, value)
+			}
+			//value = "'" + value + "'"
+			return filterJsonb(
+				s, p,
+				stringcase.ToSnakeCase(jsonFields[1]),
+				stringcase.ToSnakeCase(jsonFields[0]),
+			).
+				EQ("", value)
+		}
+		field = stringcase.ToSnakeCase(field)
 		return filterEqual(s, p, field, value)
 
 	case 2:
@@ -215,6 +252,19 @@ func oneFieldFilter(s *sql.Selector, keys []string, value string) *sql.Predicate
 			return nil
 		}
 
+		if isJsonFieldKey(field) {
+			jsonFields := splitJsonFieldKey(field)
+			if len(jsonFields) == 2 {
+				field = filterJsonbField(s,
+					stringcase.ToSnakeCase(jsonFields[1]),
+					stringcase.ToSnakeCase(jsonFields[0]),
+				)
+				//value = "'" + value + "'"
+			}
+		} else {
+			field = stringcase.ToSnakeCase(field)
+		}
+
 		var cond *sql.Predicate
 		if hasOperations(op) {
 			return processOp(s, p, op, field, value)
@@ -241,10 +291,22 @@ func oneFieldFilter(s *sql.Selector, keys []string, value string) *sql.Predicate
 
 		//var cond *sql.Predicate
 		if hasDatePart(op1) {
+			if isJsonFieldKey(field) {
+				jsonFields := splitJsonFieldKey(field)
+				if len(jsonFields) == 2 {
+					field = filterJsonbField(s, jsonFields[1], jsonFields[0])
+					//value = "'" + value + "'"
+				}
+			} else {
+				field = stringcase.ToSnakeCase(field)
+			}
+
 			str := filterDatePartField(s, op1, field)
+
 			if hasOperations(op2) {
 				return processOp(s, p, op2, str, value)
 			}
+
 			return nil
 		} else {
 			str := filterJsonbField(s, op1, field)
@@ -547,6 +609,7 @@ func filterDatePart(s *sql.Selector, p *sql.Predicate, datePart, field string) *
 	return p
 }
 
+// filterDatePartField 日期
 func filterDatePartField(s *sql.Selector, datePart, field string) string {
 	p := sql.P()
 	switch s.Builder.Dialect() {
@@ -565,12 +628,14 @@ func filterDatePartField(s *sql.Selector, datePart, field string) string {
 }
 
 // filterJsonb 提取JSONB字段
-// Postgresql: WHERE ("app_profile"."preferences" -> daily_email) = 'true'
+// Postgresql: WHERE ("app_profile"."preferences" ->> 'daily_email') = 'true'
 func filterJsonb(s *sql.Selector, p *sql.Predicate, jsonbField, field string) *sql.Predicate {
+	field = stringcase.ToSnakeCase(field)
+
 	p.Append(func(b *sql.Builder) {
 		switch s.Builder.Dialect() {
 		case dialect.Postgres:
-			b.Ident(s.C(field)).WriteString(" -> ").WriteString(jsonbField)
+			b.Ident(s.C(field)).WriteString(" ->> ").WriteString("'" + jsonbField + "'")
 			//b.Arg(strings.ToLower(value))
 			break
 
@@ -584,11 +649,14 @@ func filterJsonb(s *sql.Selector, p *sql.Predicate, jsonbField, field string) *s
 	return p
 }
 
+// filterJsonbField JSONB字段
 func filterJsonbField(s *sql.Selector, jsonbField, field string) string {
+	field = stringcase.ToSnakeCase(field)
+
 	p := sql.P()
 	switch s.Builder.Dialect() {
 	case dialect.Postgres:
-		p.Ident(s.C(field)).WriteString(" -> ").WriteString(jsonbField)
+		p.Ident(s.C(field)).WriteString(" ->> ").WriteString("'" + jsonbField + "'")
 		//b.Arg(strings.ToLower(value))
 		break
 

entgo/query/filter_test.go

@@ -681,14 +681,104 @@ func TestFilter(t *testing.T) {
 		s.Where(p)
 
 		query, args := s.Query()
-		require.Equal(t, "SELECT * FROM \"app_profile\" WHERE \"app_profile\".\"preferences\" -> daily_email = $1", query)
+		require.Equal(t, "SELECT * FROM \"app_profile\" WHERE \"app_profile\".\"preferences\" ->> 'daily_email' = $1", query)
 		require.NotEmpty(t, args)
 		require.Equal(t, args[0], "true")
 	})
 }
 
 func TestFilterJsonbField(t *testing.T) {
-	s := sql.Dialect(dialect.Postgres).Select("*").From(sql.Table("app_profile"))
-	str := filterJsonbField(s, "daily_email", "preferences")
-	fmt.Println(str)
+	t.Run("filterJsonbField", func(t *testing.T) {
+		s := sql.Dialect(dialect.Postgres).Select("*").From(sql.Table("app_profile"))
+		str := filterJsonbField(s, "daily_email", "preferences")
+		fmt.Println(str)
+		require.Equal(t, str, "\"app_profile\".\"preferences\" ->> 'daily_email'")
+	})
+
+	//////////////////////////////////////////////////////////////////////////////////////////////////////////////
+
+	t.Run("MySQL_FilterEqual", func(t *testing.T) {
+		s := sql.Dialect(dialect.MySQL).Select("*").From(sql.Table("menus"))
+
+		p := sql.P()
+
+		p = makeFieldFilter(s, []string{"meta.title"}, "tom")
+		s.Where(p)
+
+		query, args := s.Query()
+		require.Equal(t, "SELECT * FROM `menus` WHERE JSON_EXTRACT(`menus`.`meta`, '$.title') = ?", query)
+		require.NotEmpty(t, args)
+		require.Equal(t, args[0], "'tom'")
+	})
+	t.Run("PostgreSQL_FilterEqual", func(t *testing.T) {
+		s := sql.Dialect(dialect.Postgres).Select("*").From(sql.Table("menus"))
+
+		p := sql.P()
+
+		p = makeFieldFilter(s, []string{"meta.title"}, "tom")
+		s.Where(p)
+
+		query, args := s.Query()
+		require.Equal(t, "SELECT * FROM \"menus\" WHERE \"menus\".\"meta\" ->> 'title' = $1", query)
+		require.NotEmpty(t, args)
+		require.Equal(t, args[0], "'tom'")
+	})
+
+	//////////////////////////////////////////////////////////////////////////////////////////////////////////////
+
+	t.Run("MySQL_FilterNot", func(t *testing.T) {
+		s := sql.Dialect(dialect.MySQL).Select("*").From(sql.Table("users"))
+
+		p := sql.P()
+
+		p = makeFieldFilter(s, []string{"meta.title", "not"}, "tom")
+		s.Where(p)
+
+		query, args := s.Query()
+		require.Equal(t, "SELECT * FROM `users` WHERE NOT JSON_EXTRACT(`users`.`meta`, '$.title') = ?", query)
+		require.NotEmpty(t, args)
+		require.Equal(t, args[0], "'tom'")
+	})
+	t.Run("PostgreSQL_FilterNot", func(t *testing.T) {
+		s := sql.Dialect(dialect.Postgres).Select("*").From(sql.Table("users"))
+
+		p := sql.P()
+
+		p = makeFieldFilter(s, []string{"meta.title", "not"}, "tom")
+		s.Where(p)
+
+		query, args := s.Query()
+		require.Equal(t, "SELECT * FROM \"users\" WHERE NOT \"users\".\"meta\" ->> 'title' = $1", query)
+		require.NotEmpty(t, args)
+		require.Equal(t, args[0], "'tom'")
+	})
+
+	//////////////////////////////////////////////////////////////////////////////////////////////////////////////
+
+	t.Run("MySQL_FilterNot_Date", func(t *testing.T) {
+		s := sql.Dialect(dialect.MySQL).Select("*").From(sql.Table("users"))
+
+		p := sql.P()
+
+		p = makeFieldFilter(s, []string{"meta.title", "date", "not"}, "2023-01-01")
+		s.Where(p)
+
+		query, args := s.Query()
+		require.Equal(t, "SELECT * FROM `users` WHERE NOT DATE(JSON_EXTRACT(`users`.`meta`, '$.title')) = ?", query)
+		require.NotEmpty(t, args)
+		require.Equal(t, args[0], "'2023-01-01'")
+	})
+	t.Run("PostgreSQL_FilterNot_Date", func(t *testing.T) {
+		s := sql.Dialect(dialect.Postgres).Select("*").From(sql.Table("users"))
+
+		p := sql.P()
+
+		p = makeFieldFilter(s, []string{"meta.title", "date", "not"}, "2023-01-01")
+		s.Where(p)
+
+		query, args := s.Query()
+		require.Equal(t, "SELECT * FROM \"users\" WHERE NOT EXTRACT('DATE' FROM \"users\".\"meta\" ->> 'title') = $1", query)
+		require.NotEmpty(t, args)
+		require.Equal(t, args[0], "'2023-01-01'")
+	})
 }

entgo/query/query_test.go

@@ -3,7 +3,6 @@ package entgo
 import (
 	"encoding/json"
 	"fmt"
-	"strings"
 	"testing"
 
 	"entgo.io/ent/dialect"
@@ -85,11 +84,11 @@ func TestJsonCodec(t *testing.T) {
 func TestSplitQuery(t *testing.T) {
 	var keys []string
 
-	keys = strings.Split("id", "__")
+	keys = splitQueryKey("id")
 	assert.Equal(t, len(keys), 1)
 	assert.Equal(t, keys[0], "id")
 
-	keys = strings.Split("id__not", "__")
+	keys = splitQueryKey("id__not")
 	assert.Equal(t, len(keys), 2)
 	assert.Equal(t, keys[0], "id")
 	assert.Equal(t, keys[1], "not")
@@ -111,19 +110,22 @@ func TestBuildQuerySelectorDefault(t *testing.T) {
 		{"PostgreSQL_NoPagination", dialect.Postgres, "", "", true, "SELECT * FROM \"users\" ORDER BY \"users\".\"created_at\" DESC"},
 
 		{"MySQL_JsonbQuery", dialect.MySQL, "{\"preferences__daily_email\" : \"true\"}", "", true, "SELECT * FROM `users` WHERE JSON_EXTRACT(`users`.`preferences`, '$.daily_email') = ? ORDER BY `users`.`created_at` DESC"},
-		{"PostgreSQL_JsonbQuery", dialect.Postgres, "{\"preferences__daily_email\" : \"true\"}", "", true, "SELECT * FROM \"users\" WHERE \"users\".\"preferences\" -> daily_email = $1 ORDER BY \"users\".\"created_at\" DESC"},
+		{"PostgreSQL_JsonbQuery", dialect.Postgres, "{\"preferences__daily_email\" : \"true\"}", "", true, "SELECT * FROM \"users\" WHERE \"users\".\"preferences\" ->> 'daily_email' = $1 ORDER BY \"users\".\"created_at\" DESC"},
 
 		{"MySQL_DatePartQuery", dialect.MySQL, "{\"created_at__date\" : \"2023-01-01\"}", "", true, "SELECT * FROM `users` WHERE DATE(`users`.`created_at`) = ? ORDER BY `users`.`created_at` DESC"},
 		{"PostgreSQL_DatePartQuery", dialect.Postgres, "{\"created_at__date\" : \"2023-01-01\"}", "", true, "SELECT * FROM \"users\" WHERE EXTRACT('DATE' FROM \"users\".\"created_at\") = $1 ORDER BY \"users\".\"created_at\" DESC"},
 
 		{"MySQL_JsonbCombineQuery", dialect.MySQL, "{\"preferences__pub_date__not\" : \"true\"}", "", true, "SELECT * FROM `users` WHERE NOT JSON_EXTRACT(`users`.`preferences`, '$.pub_date') = ? ORDER BY `users`.`created_at` DESC"},
-		{"PostgreSQL_JsonbCombineQuery", dialect.Postgres, "{\"preferences__pub_date__not\" : \"true\"}", "", true, "SELECT * FROM \"users\" WHERE NOT \"users\".\"preferences\" -> pub_date = $1 ORDER BY \"users\".\"created_at\" DESC"},
+		{"PostgreSQL_JsonbCombineQuery", dialect.Postgres, "{\"preferences__pub_date__not\" : \"true\"}", "", true, "SELECT * FROM \"users\" WHERE NOT \"users\".\"preferences\" ->> 'pub_date' = $1 ORDER BY \"users\".\"created_at\" DESC"},
 
 		{"MySQL_DatePartCombineQuery", dialect.MySQL, "{\"pub_date__date__not\" : \"true\"}", "", true, "SELECT * FROM `users` WHERE NOT DATE(`users`.`pub_date`) = ? ORDER BY `users`.`created_at` DESC"},
 		{"PostgreSQL_DatePartCombineQuery", dialect.Postgres, "{\"pub_date__date__not\" : \"true\"}", "", true, "SELECT * FROM \"users\" WHERE NOT EXTRACT('DATE' FROM \"users\".\"pub_date\") = $1 ORDER BY \"users\".\"created_at\" DESC"},
 
 		{"MySQL_DatePartRangeQuery", dialect.MySQL, "{\"pub_date__date__range\" : \"[\\\"2023-10-25\\\", \\\"2024-10-25\\\"]\"}", "", true, "SELECT * FROM `users` WHERE DATE(`users`.`pub_date`) >= ? AND DATE(`users`.`pub_date`) <= ? ORDER BY `users`.`created_at` DESC"},
 		{"PostgreSQL_DatePartRangeQuery", dialect.Postgres, "{\"pub_date__date__range\" : \"[\\\"2023-10-25\\\", \\\"2024-10-25\\\"]\"}", "", true, "SELECT * FROM \"users\" WHERE EXTRACT('DATE' FROM \"users\".\"pub_date\") >= $1 AND EXTRACT('DATE' FROM \"users\".\"pub_date\") <= $2 ORDER BY \"users\".\"created_at\" DESC"},
+
+		{"MySQL_JsonQuery", dialect.MySQL, "{\"meta.title\" : \"preferences__daily_email\"}", "", true, "SELECT * FROM `users` WHERE JSON_EXTRACT(`users`.`meta`, '$.title') = ? ORDER BY `users`.`created_at` DESC"},
+		{"PostgreSQL_JsonQuery", dialect.Postgres, "{\"meta.title\" : \"preferences__daily_email\"}", "", true, "SELECT * FROM \"users\" WHERE \"users\".\"meta\" ->> 'title' = $1 ORDER BY \"users\".\"created_at\" DESC"},
 	}
 	for _, tc := range testcases {
 		t.Run(tc.name, func(t *testing.T) {

entgo/update/update.go

@@ -34,7 +34,7 @@ func BuildSetNullUpdater(fields []string) func(u *sql.UpdateBuilder) {
 }
 
 // ExtractJsonFieldKeyValues 提取json字段的键值对
-func ExtractJsonFieldKeyValues(msg proto.Message, paths []string) []string {
+func ExtractJsonFieldKeyValues(msg proto.Message, paths []string, needToSnakeCase bool) []string {
 	var keyValues []string
 	rft := msg.ProtoReflect()
 	for _, path := range paths {
@@ -46,7 +46,14 @@ func ExtractJsonFieldKeyValues(msg proto.Message, paths []string) []string {
 			continue
 		}
 
-		keyValues = append(keyValues, fmt.Sprintf("'%s'", stringcase.ToSnakeCase(path)))
+		var k string
+		if needToSnakeCase {
+			k = stringcase.ToSnakeCase(path)
+		} else {
+			k = path
+		}
+
+		keyValues = append(keyValues, fmt.Sprintf("'%s'", k))
 
 		v := rft.Get(fd)
 		switch v.Interface().(type) {
@@ -77,8 +84,8 @@ func SetJsonNullFieldUpdateBuilder(fieldName string, msg proto.Message, paths []
 }
 
 // SetJsonFieldValueUpdateBuilder 设置json字段的值
-func SetJsonFieldValueUpdateBuilder(fieldName string, msg proto.Message, paths []string) func(u *sql.UpdateBuilder) {
-	keyValues := ExtractJsonFieldKeyValues(msg, paths)
+func SetJsonFieldValueUpdateBuilder(fieldName string, msg proto.Message, paths []string, needToSnakeCase bool) func(u *sql.UpdateBuilder) {
+	keyValues := ExtractJsonFieldKeyValues(msg, paths, needToSnakeCase)
 	if len(keyValues) == 0 {
 		return nil
 	}

tag.bat

@@ -3,7 +3,7 @@ git tag v1.1.13
 git tag bank_card/v1.1.3
 git tag geoip/v1.1.3
 
-git tag entgo/v1.1.17
+git tag entgo/v1.1.24
 git tag gorm/v1.1.3
 
 git push origin --tags

timeutil/consts.go

@@ -1,4 +1,4 @@
-package util
+package timeutil
 
 import "time"
 

timeutil/diff.go

@@ -1,4 +1,4 @@
-package util
+package timeutil
 
 import (
 	"math"

timeutil/diff_test.go

@@ -1,4 +1,4 @@
-package util
+package timeutil
 
 import (
 	"testing"

timeutil/format.go

@@ -1,4 +1,4 @@
-package util
+package timeutil
 
 import (
 	"fmt"

timeutil/range.go

@@ -1,4 +1,4 @@
-package util
+package timeutil
 
 import "time"
 

timeutil/range_test.go

@@ -1,4 +1,4 @@
-package util
+package timeutil
 
 import (
 	"fmt"

timeutil/trans.go

@@ -1,8 +1,9 @@
-package util
+package timeutil
 
 import (
 	"time"
 
+	"google.golang.org/protobuf/types/known/durationpb"
 	"google.golang.org/protobuf/types/known/timestamppb"
 
 	"github.com/tx7do/go-utils/trans"
@@ -132,3 +133,19 @@ func TimeToTimestamppb(tm *time.Time) *timestamppb.Timestamp {
 	}
 	return nil
 }
+
+func FloatToDurationpb(duration *float64, timePrecision time.Duration) *durationpb.Duration {
+	if duration == nil {
+		return nil
+	}
+	return durationpb.New(time.Duration(*duration) * timePrecision)
+}
+
+func DurationpbToFloat(duration *durationpb.Duration, timePrecision time.Duration) *float64 {
+	if duration == nil {
+		return nil
+	}
+	seconds := duration.AsDuration().Seconds()
+	secondsWithPrecision := seconds / timePrecision.Seconds()
+	return &secondsWithPrecision
+}

timeutil/trans_test.go

@@ -1,4 +1,4 @@
-package util
+package timeutil
 
 import (
 	"fmt"