/
queryset.go
222 lines (197 loc) · 6.13 KB
/
queryset.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
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
package pgtalk
import (
"context"
"fmt"
"reflect"
)
type QuerySet[T any] struct {
unimplementedBooleanExpression
preparedName string
tableInfo TableInfo
tableAliasOverride string
selectors []ColumnAccessor
distinct bool
condition SQLWriter
limit int
offset int
groupBy []ColumnAccessor
having SQLExpression
orderBy []SQLWriter
sortOption string
}
func MakeQuerySet[T any](tableInfo TableInfo, selectors []ColumnAccessor) QuerySet[T] {
return QuerySet[T]{
tableInfo: tableInfo,
selectors: selectors,
condition: EmptyCondition}
}
// querySet
func (q QuerySet[T]) selectAccessors() []ColumnAccessor { return q.selectors }
func (q QuerySet[T]) whereCondition() SQLWriter { return q.condition }
func (q QuerySet[T]) fromSectionOn(w WriteContext) {
fmt.Fprintf(w, "%s.%s %s", q.tableInfo.Schema, q.tableInfo.Name, w.TableAlias(q.tableInfo.Name, q.tableInfo.Alias))
}
func (q QuerySet[T]) augmentedContext(w WriteContext) WriteContext {
if q.tableAliasOverride != "" {
return w.WithAlias(q.tableInfo.Name, q.tableAliasOverride)
}
return w
}
func (q QuerySet[T]) SQLOn(w WriteContext) {
w = q.augmentedContext(w)
fmt.Fprint(w, "SELECT")
if q.distinct {
fmt.Fprint(w, " DISTINCT\n")
} else {
fmt.Fprint(w, "\n")
}
writeAccessOn(q.selectors, w)
fmt.Fprint(w, "\nFROM ")
q.fromSectionOn(w)
if _, ok := q.condition.(noCondition); !ok {
fmt.Fprint(w, "\nWHERE ")
q.condition.SQLOn(w)
}
if len(q.groupBy) > 0 {
fmt.Fprint(w, "\nGROUP BY\n")
writeAccessOn(q.groupBy, w)
}
if q.having != nil {
fmt.Fprint(w, "\nHAVING ")
q.having.SQLOn(w)
}
if len(q.orderBy) > 0 {
fmt.Fprint(w, "\nORDER BY\n")
writeListOn(q.orderBy, w)
}
if q.sortOption != "" {
fmt.Fprint(w, " ", q.sortOption)
}
if q.limit > 0 {
fmt.Fprintf(w, "\nLIMIT %d", q.limit)
}
if q.offset > 0 {
fmt.Fprintf(w, "\nOFFSET %d", q.offset)
}
}
// TableAlias will override the default table or view alias
func (q QuerySet[T]) TableAlias(alias string) QuerySet[T] { q.tableAliasOverride = alias; return q }
// Named sets the name for preparing the statement
func (q QuerySet[T]) Named(preparedName string) QuerySet[T] { q.preparedName = preparedName; return q }
// Distinct is a SQL instruction
func (q QuerySet[T]) Distinct() QuerySet[T] { q.distinct = true; return q }
// Ascending is a SQL instruction for ASC sort option
func (q QuerySet[T]) Ascending() QuerySet[T] { q.sortOption = "ASC"; return q }
// Descending is a SQL instruction for DESC sort option
func (q QuerySet[T]) Descending() QuerySet[T] { q.sortOption = "DESC"; return q }
// Where is a SQL instruction
func (q QuerySet[T]) Where(condition SQLExpression) QuerySet[T] { q.condition = condition; return q }
// Limit is a SQL instruction
func (q QuerySet[T]) Limit(limit int) QuerySet[T] { q.limit = limit; return q }
// Offset is a SQL instruction
func (q QuerySet[T]) Offset(offset int) QuerySet[T] { q.offset = offset; return q }
// GroupBy is a SQL instruction
func (q QuerySet[T]) GroupBy(cas ...ColumnAccessor) QuerySet[T] {
q.groupBy = cas
return q
}
func (q QuerySet[T]) Having(condition SQLExpression) QuerySet[T] { q.having = condition; return q }
func (q QuerySet[T]) OrderBy(cas ...SQLWriter) QuerySet[T] {
q.orderBy = cas
return q
}
func (q QuerySet[T]) Exists() unaryExpression {
return unaryExpression{Operator: "EXISTS", Operand: q}
}
func (d QuerySet[T]) Iterate(ctx context.Context, conn querier, parameters ...*QueryParameter) (*resultIterator[T], error) {
params := argumentValues(parameters)
rows, err := conn.Query(ctx, SQL(d), params...)
return &resultIterator[T]{
queryError: err,
rows: rows,
selectors: d.selectors,
}, err
}
func (d QuerySet[T]) Exec(ctx context.Context, conn querier, parameters ...*QueryParameter) (list []*T, err error) {
params := argumentValues(parameters)
rows, err := conn.Query(ctx, SQL(d), params...)
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
entity := new(T)
sw := []any{}
for _, each := range d.selectors {
sw = append(sw, each.FieldValueToScan(entity))
}
if err := rows.Scan(sw...); err != nil {
return list, err
}
list = append(list, entity)
}
return
}
// ExecIntoMaps executes the query and returns a list of generic maps (column->value).
// This can be used if you do not want to get full records types or have multiple custom values.
func (d QuerySet[T]) ExecIntoMaps(ctx context.Context, conn querier, parameters ...*QueryParameter) (list []map[string]any, err error) {
return execIntoMaps(ctx, conn, SQL(d), d.selectors, parameters...)
}
func execIntoMaps(ctx context.Context, conn querier, query string, selectors []ColumnAccessor, parameters ...*QueryParameter) (list []map[string]any, err error) {
params := argumentValues(parameters)
rows, err := conn.Query(ctx, query, params...)
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
sw := []any{} // sw holds addresses to the valueToInsert
for _, each := range selectors {
sw = each.AppendScannable(sw)
}
if err := rows.Scan(sw...); err != nil {
return list, err
}
row := map[string]any{}
for i, each := range selectors {
// sw[i] is the address of the valueToInsert of each (ColumnAccessor)
// use reflect version of dereferencing
rv := reflect.ValueOf(sw[i])
row[each.Column().columnName] = rv.Elem().Interface()
}
list = append(list, row)
}
return
}
func (d QuerySet[T]) Join(otherQuerySet querySet) join {
return join{
leftSet: d,
rightSet: otherQuerySet,
joinType: innerJoinType,
}
}
func (d QuerySet[T]) LeftOuterJoin(otherQuerySet querySet) join {
return join{
leftSet: d,
rightSet: otherQuerySet,
joinType: leftOuterJoinType,
}
}
// Deprecated: use RightOuterJoin
func (d QuerySet[T]) RightJoin(otherQuerySet querySet) join {
return d.RightOuterJoin(otherQuerySet)
}
func (d QuerySet[T]) RightOuterJoin(otherQuerySet querySet) join {
return join{
leftSet: d,
rightSet: otherQuerySet,
joinType: rightOuterJoinType,
}
}
func (d QuerySet[T]) FullJoin(otherQuerySet querySet) join {
return join{
leftSet: d,
rightSet: otherQuerySet,
joinType: fullOuterJoinType,
}
}