feat: entgo.

entgo/query/filter.go

@@ -63,16 +63,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 +99,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 +141,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 +198,15 @@ 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 := strings.Split(key, "__")
+		keys := splitQueryKey(key)
 
-		if cond := oneFieldFilter(s, keys, v); cond != nil {
+		if cond := makeFieldFilter(s, keys, v); cond != nil {
 			ps = append(ps, cond)
 		}
 	}
@@ -165,32 +214,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 +232,14 @@ 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 {
+				return filterEqual(s, p, field, value)
+			}
+			value = "'" + value + "'"
+			return filterJsonb(s, p, jsonFields[1], jsonFields[0]).EQ("", value)
+		}
 		return filterEqual(s, p, field, value)
 
 	case 2:
@@ -215,6 +248,14 @@ 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, jsonFields[1], jsonFields[0])
+				value = "'" + value + "'"
+			}
+		}
+
 		var cond *sql.Predicate
 		if hasOperations(op) {
 			return processOp(s, p, op, field, value)
@@ -241,10 +282,19 @@ 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 + "'"
+				}
+			}
+
 			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 +597,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() {
@@ -584,6 +635,7 @@ 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 {
 	p := sql.P()
 	switch s.Builder.Dialect() {

entgo/query/filter_test.go

@@ -7,7 +7,6 @@ import (
 	"entgo.io/ent/dialect"
 	"entgo.io/ent/dialect/sql"
 
-	"github.com/stretchr/testify/assert"
 	"github.com/stretchr/testify/require"
 )
 
@@ -689,8 +688,97 @@ func TestFilter(t *testing.T) {
 }
 
 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)
-	assert.Equal(t, str, "\"app_profile\".\"preferences\" ->> 'daily_email'")
+	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")

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.20
+git tag entgo/v1.1.22
 git tag gorm/v1.1.3
 
 git push origin --tags