1# Expressions
2
3`goqu` provides an idiomatic DSL for generating SQL. Datasets only act as a clause builder (i.e. Where, From, Select), most of these clause methods accept Expressions which are the building blocks for your SQL statement, you can think of them as fragments of SQL.
4
5* [`Ex{}`](#ex) - A map where the key will become an Identifier and the Key is the value, this is most commonly used in the Where clause.
6* [`ExOr{}`](#ex-or)- OR version of `Ex`. A map where the key will become an Identifier and the Key is the value, this is most commonly used in the Where clause
7* [`S`](#S) - An Identifier that represents a schema. With a schema identifier you can fully qualify tables and columns.
8* [`T`](#T) - An Identifier that represents a Table. With a Table identifier you can fully qualify columns.
9* [`C`](#C) - An Identifier that represents a Column. See the docs for more examples
10* [`I`](#I) - An Identifier represents a schema, table, or column or any combination. I parses identifiers seperated by a . character.
11* [`L`](#L) - An SQL literal.
12* [`V`](#V) - An Value to be used in SQL.
13* [`And`](#and) - AND multiple expressions together.
14* [`Or`](#or) - OR multiple expressions together.
15* [Complex Example](#complex) - Complex Example using most of the Expression DSL.
16
17The entry points for expressions are:
18
19<a name="ex"></a>
20**[`Ex{}`](https://godoc.org/github.com/doug-martin/goqu#Ex)**
21
22A map where the key will become an Identifier and the Key is the value, this is most commonly used in the Where clause. By default `Ex` will use the equality operator except in cases where the equality operator will not work, see the example below.
23
24```go
25sql, _, _ := db.From("items").Where(goqu.Ex{
26 "col1": "a",
27 "col2": 1,
28 "col3": true,
29 "col4": false,
30 "col5": nil,
31 "col6": []string{"a", "b", "c"},
32}).ToSQL()
33fmt.Println(sql)
34```
35
36Output:
37```sql
38SELECT * FROM "items" WHERE (("col1" = 'a') AND ("col2" = 1) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IS NULL) AND ("col6" IN ('a', 'b', 'c')))
39```
40
41You can also use the [`Op`](https://godoc.org/github.com/doug-martin/goqu#Op) map which allows you to create more complex expressions using the map syntax. When using the `Op` map the key is the name of the comparison you want to make (e.g. `"neq"`, `"like"`, `"is"`, `"in"`), the key is case insensitive.
42
43```go
44sql, _, _ := db.From("items").Where(goqu.Ex{
45 "col1": goqu.Op{"neq": "a"},
46 "col3": goqu.Op{"isNot": true},
47 "col6": goqu.Op{"notIn": []string{"a", "b", "c"}},
48}).ToSQL()
49fmt.Println(sql)
50```
51
52Output:
53```sql
54SELECT * FROM "items" WHERE (("col1" != 'a') AND ("col3" IS NOT TRUE) AND ("col6" NOT IN ('a', 'b', 'c')))
55```
56For a more complete examples see the [`Op`](https://godoc.org/github.com/doug-martin/goqu#Op) and [`Ex`](https://godoc.org/github.com/doug-martin/goqu#Ex) docs
57
58<a name="ex-or"></a>
59**[`ExOr{}`](https://godoc.org/github.com/doug-martin/goqu#ExOr)**
60
61A map where the key will become an Identifier and the Key is the value, this is most commonly used in the Where clause. By default `ExOr` will use the equality operator except in cases where the equality operator will not work, see the example below.
62
63```go
64sql, _, _ := db.From("items").Where(goqu.ExOr{
65 "col1": "a",
66 "col2": 1,
67 "col3": true,
68 "col4": false,
69 "col5": nil,
70 "col6": []string{"a", "b", "c"},
71}).ToSQL()
72fmt.Println(sql)
73```
74
75Output:
76```sql
77SELECT * FROM "items" WHERE (("col1" = 'a') OR ("col2" = 1) OR ("col3" IS TRUE) OR ("col4" IS FALSE) OR ("col5" IS NULL) OR ("col6" IN ('a', 'b', 'c')))
78```
79
80You can also use the [`Op`](https://godoc.org/github.com/doug-martin/goqu#Op) map which allows you to create more complex expressions using the map syntax. When using the `Op` map the key is the name of the comparison you want to make (e.g. `"neq"`, `"like"`, `"is"`, `"in"`), the key is case insensitive.
81
82```go
83sql, _, _ := db.From("items").Where(goqu.ExOr{
84 "col1": goqu.Op{"neq": "a"},
85 "col3": goqu.Op{"isNot": true},
86 "col6": goqu.Op{"notIn": []string{"a", "b", "c"}},
87}).ToSQL()
88fmt.Println(sql)
89```
90
91Output:
92```sql
93SELECT * FROM "items" WHERE (("col1" != 'a') OR ("col3" IS NOT TRUE) OR ("col6" NOT IN ('a', 'b', 'c')))
94```
95For a more complete examples see the [`Op`](https://godoc.org/github.com/doug-martin/goqu#Op) and [`ExOr`](https://godoc.org/github.com/doug-martin/goqu#Ex) docs
96
97<a name="S"></a>
98**[`S()`](https://godoc.org/github.com/doug-martin/goqu#S)**
99
100An Identifier that represents a schema. With a schema identifier you can fully qualify tables and columns.
101
102```go
103s := goqu.S("my_schema")
104
105// "my_schema"."my_table"
106t := s.Table("my_table")
107
108// "my_schema"."my_table"."my_column"
109
110sql, _, _ := goqu.From(t).Select(t.Col("my_column").ToSQL()
111// SELECT "my_schema"."my_table"."my_column" FROM "my_schema"."my_table"
112fmt.Println(sql)
113```
114
115<a name="T"></a>
116**[`T()`](https://godoc.org/github.com/doug-martin/goqu#T)**
117
118An Identifier that represents a Table. With a Table identifier you can fully qualify columns.
119```go
120t := s.Table("my_table")
121
122sql, _, _ := goqu.From(t).Select(t.Col("my_column").ToSQL()
123// SELECT "my_table"."my_column" FROM "my_table"
124fmt.Println(sql)
125
126// qualify the table with a schema
127sql, _, _ := goqu.From(t.Schema("my_schema")).Select(t.Col("my_column").ToSQL()
128// SELECT "my_table"."my_column" FROM "my_schema"."my_table"
129fmt.Println(sql)
130```
131
132<a name="C"></a>
133**[`C()`](https://godoc.org/github.com/doug-martin/goqu#C)**
134
135An Identifier that represents a Column. See the [docs]((https://godoc.org/github.com/doug-martin/goqu#C)) for more examples
136
137```go
138sql, _, _ := goqu.From("table").Where(goqu.C("col").Eq(10)).ToSQL()
139// SELECT * FROM "table" WHERE "col" = 10
140fmt.Println(sql)
141```
142
143<a name="I"></a>
144**[`I()`](https://godoc.org/github.com/doug-martin/goqu#I)**
145
146An Identifier represents a schema, table, or column or any combination. `I` parses identifiers seperated by a `.` character.
147
148```go
149// with three parts it is assumed you have provided a schema, table and column
150goqu.I("my_schema.table.col") == goqu.S("my_schema").Table("table").Col("col")
151
152// with two parts it is assumed you have provided a table and column
153goqu.I("table.col") == goqu.T("table").Col("col")
154
155// with a single value it is the same as calling goqu.C
156goqu.I("col") == goqu.C("col")
157```
158
159<a name="L"></a>
160**[`L()`](https://godoc.org/github.com/doug-martin/goqu#L)**
161
162An SQL literal. You may find yourself in a situation where an IdentifierExpression cannot expression an SQL fragment that your database supports. In that case you can use a LiteralExpression
163
164```go
165// manual casting
166goqu.L(`"json"::TEXT = "other_json"::text`)
167
168// custom function invocation
169goqu.L(`custom_func("a")`)
170
171// postgres JSON access
172goqu.L(`"json_col"->>'someField'`).As("some_field")
173```
174
175You can also use placeholders in your literal with a `?` character. `goqu` will handle changing it to what the dialect needs (e.g. `?` mysql, `$1` postgres, `?` sqlite3).
176
177**NOTE** If your query is not prepared the placeholders will be properly interpolated.
178
179```go
180goqu.L("col IN (?, ?, ?)", "a", "b", "c")
181```
182
183Putting it together
184
185```go
186ds := db.From("test").Where(
187 goqu.L(`("json"::TEXT = "other_json"::TEXT)`),
188 goqu.L("col IN (?, ?, ?)", "a", "b", "c"),
189)
190
191sql, args, _ := ds.ToSQL()
192fmt.Println(sql, args)
193
194sql, args, _ := ds.Prepared(true).ToSQL()
195fmt.Println(sql, args)
196```
197
198Output:
199```sql
200SELECT * FROM "test" WHERE ("json"::TEXT = "other_json"::TEXT) AND col IN ('a', 'b', 'c') []
201-- assuming postgres dialect
202SELECT * FROM "test" WHERE ("json"::TEXT = "other_json"::TEXT) AND col IN ($1, $2, $3) [a, b, c]
203```
204
205<a name="V"></a>
206**[`V()`](https://godoc.org/github.com/doug-martin/goqu#V)**
207
208Sometimes you may have a value that you want to use directly in SQL.
209
210**NOTE** This is a shorter version of `goqu.L("?", val)`
211
212For example you may want to select a value as a column.
213
214```go
215ds := goqu.From("user").Select(
216 goqu.V(true).As("is_verified"),
217 goqu.V(1.2).As("version"),
218 "first_name",
219 "last_name",
220)
221
222sql, args, _ := ds.ToSQL()
223fmt.Println(sql, args)
224```
225
226Output:
227```
228SELECT TRUE AS "is_verified", 1.2 AS "version", "first_name", "last_name" FROM "user" []
229```
230
231You can also use `goqu.V` in where clauses.
232
233```
234ds := goqu.From("user").Where(goqu.V(1).Neq(1))
235sql, args, _ := ds.ToSQL()
236fmt.Println(sql, args)
237```
238
239Output:
240
241```
242SELECT * FROM "user" WHERE (1 != 1) []
243```
244
245You can also use them in prepared statements.
246
247```
248ds := goqu.From("user").Where(goqu.V(1).Neq(1))
249sql, args, _ := ds.Prepared(true).ToSQL()
250fmt.Println(sql, args)
251```
252
253Output:
254
255```
256SELECT * FROM "user" WHERE (? != ?) [1, 1]
257```
258
259
260<a name="and"></a>
261**[`And()`](https://godoc.org/github.com/doug-martin/goqu#And)**
262
263You can use the `And` function to AND multiple expressions together.
264
265**NOTE** By default goqu will AND expressions together
266
267```go
268ds := goqu.From("test").Where(
269 goqu.And(
270 goqu.C("col").Gt(10),
271 goqu.C("col").Lt(20),
272 ),
273)
274sql, args, _ := ds.ToSQL()
275fmt.Println(sql, args)
276
277sql, args, _ = ds.Prepared(true).ToSQL()
278fmt.Println(sql, args)
279```
280
281Output:
282```sql
283SELECT * FROM "test" WHERE (("col" > 10) AND ("col" < 20)) []
284SELECT * FROM "test" WHERE (("col" > ?) AND ("col" < ?)) [10 20]
285```
286
287<a name="or"></a>
288**[`Or()`](https://godoc.org/github.com/doug-martin/goqu#Or)**
289
290You can use the `Or` function to OR multiple expressions together.
291
292```go
293ds := goqu.From("test").Where(
294 goqu.Or(
295 goqu.C("col").Eq(10),
296 goqu.C("col").Eq(20),
297 ),
298)
299sql, args, _ := ds.ToSQL()
300fmt.Println(sql, args)
301
302sql, args, _ = ds.Prepared(true).ToSQL()
303fmt.Println(sql, args)
304```
305
306Output:
307```sql
308SELECT * FROM "test" WHERE (("col" = 10) OR ("col" = 20)) []
309SELECT * FROM "test" WHERE (("col" = ?) OR ("col" = ?)) [10 20]
310```
311
312You can also use `Or` and `And` functions in tandem which will give you control not only over how the Expressions are joined together, but also how they are grouped
313
314```go
315ds := goqu.From("items").Where(
316 goqu.Or(
317 goqu.C("a").Gt(10),
318 goqu.And(
319 goqu.C("b").Eq(100),
320 goqu.C("c").Neq("test"),
321 ),
322 ),
323)
324sql, args, _ := ds.ToSQL()
325fmt.Println(sql, args)
326
327sql, args, _ = ds.Prepared(true).ToSQL()
328fmt.Println(sql, args)
329```
330
331Output:
332```sql
333SELECT * FROM "items" WHERE (("a" > 10) OR (("b" = 100) AND ("c" != 'test'))) []
334SELECT * FROM "items" WHERE (("a" > ?) OR (("b" = ?) AND ("c" != ?))) [10 100 test]
335```
336
337You can also use Or with the map syntax
338```go
339ds := goqu.From("test").Where(
340 goqu.Or(
341 // Ex will be anded together
342 goqu.Ex{
343 "col1": 1,
344 "col2": true,
345 },
346 goqu.Ex{
347 "col3": nil,
348 "col4": "foo",
349 },
350 ),
351)
352sql, args, _ := ds.ToSQL()
353fmt.Println(sql, args)
354
355sql, args, _ = ds.Prepared(true).ToSQL()
356fmt.Println(sql, args)
357```
358
359Output:
360```sql
361SELECT * FROM "test" WHERE ((("col1" = 1) AND ("col2" IS TRUE)) OR (("col3" IS NULL) AND ("col4" = 'foo'))) []
362SELECT * FROM "test" WHERE ((("col1" = ?) AND ("col2" IS TRUE)) OR (("col3" IS NULL) AND ("col4" = ?))) [1 foo]
363```
364
365<a name="complex"></a>
366## Complex Example
367
368This example uses most of the features of the `goqu` Expression DSL
369
370```go
371ds := db.From("test").
372 Select(goqu.COUNT("*")).
373 InnerJoin(goqu.I("test2"), goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.id")})).
374 LeftJoin(goqu.I("test3"), goqu.On(goqu.Ex{"test2.fkey": goqu.I("test3.id")})).
375 Where(
376 goqu.Ex{
377 "test.name": goqu.Op{"like": regexp.MustCompile("^(a|b)")},
378 "test2.amount": goqu.Op{"isNot": nil},
379 },
380 goqu.ExOr{
381 "test3.id": nil,
382 "test3.status": []string{"passed", "active", "registered"},
383 },
384 ).
385 Order(goqu.I("test.created").Desc().NullsLast()).
386 GroupBy(goqu.I("test.user_id")).
387 Having(goqu.AVG("test3.age").Gt(10))
388
389sql, args, _ := ds.ToSQL()
390fmt.Println(sql)
391
392sql, args, _ := ds.Prepared(true).ToSQL()
393fmt.Println(sql)
394```
395
396Using the Expression syntax
397```go
398ds := db.From("test").
399 Select(goqu.COUNT("*")).
400 InnerJoin(goqu.I("test2"), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.id")))).
401 LeftJoin(goqu.I("test3"), goqu.On(goqu.I("test2.fkey").Eq(goqu.I("test3.id")))).
402 Where(
403 goqu.I("test.name").Like(regexp.MustCompile("^(a|b)")),
404 goqu.I("test2.amount").IsNotNull(),
405 goqu.Or(
406 goqu.I("test3.id").IsNull(),
407 goqu.I("test3.status").In("passed", "active", "registered"),
408 ),
409 ).
410 Order(goqu.I("test.created").Desc().NullsLast()).
411 GroupBy(goqu.I("test.user_id")).
412 Having(goqu.AVG("test3.age").Gt(10))
413
414sql, args, _ := ds.ToSQL()
415fmt.Println(sql)
416
417sql, args, _ := ds.Prepared(true).ToSQL()
418fmt.Println(sql)
419```
420
421Both examples generate the following SQL
422
423```sql
424-- interpolated
425SELECT COUNT(*)
426FROM "test"
427 INNER JOIN "test2" ON ("test"."fkey" = "test2"."id")
428 LEFT JOIN "test3" ON ("test2"."fkey" = "test3"."id")
429WHERE ((("test"."name" ~ '^(a|b)') AND ("test2"."amount" IS NOT NULL)) AND
430 (("test3"."id" IS NULL) OR ("test3"."status" IN ('passed', 'active', 'registered'))))
431GROUP BY "test"."user_id"
432HAVING (AVG("test3"."age") > 10)
433ORDER BY "test"."created" DESC NULLS LAST []
434
435-- prepared
436SELECT COUNT(*)
437FROM "test"
438 INNER JOIN "test2" ON ("test"."fkey" = "test2"."id")
439 LEFT JOIN "test3" ON ("test2"."fkey" = "test3"."id")
440WHERE ((("test"."name" ~ ?) AND ("test2"."amount" IS NOT NULL)) AND
441 (("test3"."id" IS NULL) OR ("test3"."status" IN (?, ?, ?))))
442GROUP BY "test"."user_id"
443HAVING (AVG("test3"."age") > ?)
444ORDER BY "test"."created" DESC NULLS LAST [^(a|b) passed active registered 10]
445```
446
447
View as plain text