/
query.go
161 lines (139 loc) · 3.28 KB
/
query.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
package postgres
import (
"fmt"
"gorm.io/gorm"
)
const (
Equal = "Equal"
LessThan = "LessThan"
GreaterThan = "GreaterThan"
GreaterThanEqual = "GreaterThanEqual"
LessThanEqual = "LessThanEqual"
IN = "IN"
JSON = "JSON"
Like = "Like"
Descending = "Descending"
Ascending = "Ascending"
)
var (
validOrdering = map[string]bool{
Descending: true,
Ascending: true,
}
)
// Filter sql query
type Filter struct {
Condition string
Field string
Value interface{}
}
// Ordering set ordering result
type Ordering struct {
Field string
Direction string
}
// Query preparation sql
type Query struct {
Model string
Limit int
Offset int
Filters []*Filter
Orderings []*Ordering
}
// NewQuery sql
func NewQuery(model string) *Query {
return &Query{
Model: model,
}
}
// NewFilter creates a new property Filter
func NewFilter(field, condition string, value interface{}) *Filter {
return &Filter{
Field: field,
Condition: condition,
Value: value,
}
}
// NewOrdering create a new property Ordering
func NewOrdering(field, direction string) *Ordering {
d := direction
if !validOrdering[direction] {
d = Descending
}
return &Ordering{
Field: field,
Direction: d,
}
}
// Filter adds a filter to the query
func (q *Query) Filter(property, condition string, value interface{}) *Query {
filter := NewFilter(property, condition, value)
q.Filters = append(q.Filters, filter)
return q
}
// Ordering adds a sort order to the query
func (q *Query) Ordering(property, direction string) *Query {
order := NewOrdering(property, direction)
q.Orderings = append(q.Orderings, order)
return q
}
// Slice result from database
func (q *Query) Slice(offset, limit int) *Query {
q.Offset = offset
q.Limit = limit
return q
}
// TranslateQuery from struct to gorm.DB query
func TranslateQuery(db *gorm.DB, query *Query) *gorm.DB {
for _, filter := range query.Filters {
switch filter.Condition {
case Equal:
q := fmt.Sprintf("%s = ?", filter.Field)
db = db.Where(q, filter.Value)
case GreaterThan:
q := fmt.Sprintf("%s > ?", filter.Field)
db = db.Where(q, filter.Value)
case GreaterThanEqual:
q := fmt.Sprintf("%s >= ?", filter.Field)
db = db.Where(q, filter.Value)
case LessThan:
q := fmt.Sprintf("%s < ?", filter.Field)
db = db.Where(q, filter.Value)
case LessThanEqual:
q := fmt.Sprintf("%s <= ?", filter.Field)
db = db.Where(q, filter.Value)
case JSON:
q := fmt.Sprintf("%s @> ?", filter.Field)
db = db.Where(q, filter.Value)
case IN:
q := fmt.Sprintf("%s IN (?)", filter.Field)
db = db.Where(q, filter.Value)
case Like:
q := fmt.Sprintf("%s LIKE ?", filter.Field)
db = db.Where(q, filter.Value)
default:
q := fmt.Sprintf("%s = ?", filter.Field)
db = db.Where(q, filter.Value)
}
}
for _, order := range query.Orderings {
switch order.Direction {
case Ascending:
o := fmt.Sprintf("%s %s", order.Field, "ASC")
db = db.Order(o)
case Descending:
o := fmt.Sprintf("%s %s", order.Field, "DESC")
db = db.Order(o)
default:
o := fmt.Sprintf("%s %s", order.Field, "DESC")
db = db.Order(o)
}
}
if query.Offset > 0 {
db = db.Offset(query.Offset)
}
if query.Limit > 0 {
db = db.Limit(query.Limit)
}
return db
}