1# Selecting
2
3* [Creating a SelectDataset](#create)
4* Building SQL
5 * [`Select`](#select)
6 * [`Distinct`](#distinct)
7 * [`From`](#from)
8 * [`Join`](#joins)
9 * [`Where`](#where)
10 * [`Limit`](#limit)
11 * [`Offset`](#offset)
12 * [`GroupBy`](#group_by)
13 * [`Having`](#having)
14 * [`Window`](#window)
15 * [`With`](#with)
16 * [`SetError`](#seterror)
17 * [`ForUpdate`](#forupdate)
18* Executing Queries
19 * [`ScanStructs`](#scan-structs) - Scans rows into a slice of structs
20 * [`ScanStruct`](#scan-struct) - Scans a row into a slice a struct, returns false if a row wasnt found
21 * [`ScanVals`](#scan-vals)- Scans a rows of 1 column into a slice of primitive values
22 * [`ScanVal`](#scan-val) - Scans a row of 1 column into a primitive value, returns false if a row wasnt found.
23 * [`Scanner`](#scanner) - Allows you to interatively scan rows into structs or values.
24 * [`Count`](#count) - Returns the count for the current query
25 * [`Pluck`](#pluck) - Selects a single column and stores the results into a slice of primitive values
26
27<a name="create"></a>
28To create a [`SelectDataset`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset) you can use
29
30**[`goqu.From`](https://godoc.org/github.com/doug-martin/goqu/#From) and [`goqu.Select`](https://godoc.org/github.com/doug-martin/goqu/#Select)**
31
32When you just want to create some quick SQL, this mostly follows the `Postgres` with the exception of placeholders for prepared statements.
33
34```go
35sql, _, _ := goqu.From("table").ToSQL()
36fmt.Println(sql)
37
38sql, _, _ := goqu.Select(goqu.L("NOW()")).ToSQL()
39fmt.Println(sql)
40```
41Output:
42```
43SELECT * FROM "table"
44SELECT NOW()
45```
46
47**[`DialectWrapper.From`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.From) and [`DialectWrapper.Select`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.Select)**
48
49Use this when you want to create SQL for a specific `dialect`
50
51```go
52// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
53
54dialect := goqu.Dialect("mysql")
55
56sql, _, _ := dialect.From("table").ToSQL()
57fmt.Println(sql)
58
59sql, _, _ := dialect.Select(goqu.L("NOW()")).ToSQL()
60fmt.Println(sql)
61```
62Output:
63```
64SELECT * FROM `table`
65SELECT NOW()
66```
67
68**[`Database.From`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.From) and [`Database.Select`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.From)**
69
70Use this when you want to execute the SQL or create SQL for the drivers dialect.
71
72```go
73// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
74
75mysqlDB := //initialize your db
76db := goqu.New("mysql", mysqlDB)
77
78sql, _, _ := db.From("table").ToSQL()
79fmt.Println(sql)
80
81sql, _, _ := db.Select(goqu.L("NOW()")).ToSQL()
82fmt.Println(sql)
83```
84Output:
85```
86SELECT * FROM `table`
87SELECT NOW()
88```
89
90### Examples
91
92For more examples visit the **[Docs](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset)**
93
94<a name="select"></a>
95**[`Select`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Select)**
96
97```go
98sql, _, _ := goqu.From("test").Select("a", "b", "c").ToSQL()
99fmt.Println(sql)
100```
101
102Output:
103```sql
104SELECT "a", "b", "c" FROM "test"
105```
106
107You can also ues another dataset in your select
108
109```go
110ds := goqu.From("test")
111fromDs := ds.Select("age").Where(goqu.C("age").Gt(10))
112sql, _, _ := ds.From().Select(fromDs).ToSQL()
113fmt.Println(sql)
114```
115
116Output:
117```
118SELECT (SELECT "age" FROM "test" WHERE ("age" > 10))
119```
120
121Selecting a literal
122
123```go
124sql, _, _ := goqu.From("test").Select(goqu.L("a + b").As("sum")).ToSQL()
125fmt.Println(sql)
126```
127
128Output:
129```
130SELECT a + b AS "sum" FROM "test"
131```
132
133Select aggregate functions
134
135```go
136sql, _, _ := goqu.From("test").Select(
137 goqu.COUNT("*").As("age_count"),
138 goqu.MAX("age").As("max_age"),
139 goqu.AVG("age").As("avg_age"),
140).ToSQL()
141fmt.Println(sql)
142```
143
144Output:
145```
146SELECT COUNT(*) AS "age_count", MAX("age") AS "max_age", AVG("age") AS "avg_age" FROM "test"
147```
148
149Selecting columns from a struct
150
151```go
152ds := goqu.From("test")
153
154type myStruct struct {
155 Name string
156 Address string `db:"address"`
157 EmailAddress string `db:"email_address"`
158}
159
160// Pass with pointer
161sql, _, _ := ds.Select(&myStruct{}).ToSQL()
162fmt.Println(sql)
163```
164
165Output:
166```
167SELECT "address", "email_address", "name" FROM "test"
168```
169
170<a name="distinct"></a>
171**[`Distinct`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Distinct)**
172
173```go
174sql, _, _ := goqu.From("test").Select("a", "b").Distinct().ToSQL()
175fmt.Println(sql)
176```
177
178Output:
179```
180SELECT DISTINCT "a", "b" FROM "test"
181```
182
183If you dialect supports `DISTINCT ON` you provide arguments to the `Distinct` method.
184
185**NOTE** currently only the `postgres` and the default dialects support `DISTINCT ON` clauses
186
187```go
188sql, _, _ := goqu.From("test").Distinct("a").ToSQL()
189fmt.Println(sql)
190```
191Output:
192
193```
194SELECT DISTINCT ON ("a") * FROM "test"
195```
196
197You can also provide other expression arguments
198
199With `goqu.L`
200
201```go
202sql, _, _ := goqu.From("test").Distinct(goqu.L("COALESCE(?, ?)", goqu.C("a"), "empty")).ToSQL()
203fmt.Println(sql)
204```
205Output:
206```
207SELECT DISTINCT ON (COALESCE("a", 'empty')) * FROM "test"
208```
209With `goqu.Coalesce`
210```go
211sql, _, _ := goqu.From("test").Distinct(goqu.COALESCE(goqu.C("a"), "empty")).ToSQL()
212fmt.Println(sql)
213```
214Output:
215```
216SELECT DISTINCT ON (COALESCE("a", 'empty')) * FROM "test"
217```
218
219<a name="from"></a>
220**[`From`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.From)**
221
222Overriding the original from
223```go
224ds := goqu.From("test")
225sql, _, _ := ds.From("test2").ToSQL()
226fmt.Println(sql)
227```
228
229Output:
230```
231SELECT * FROM "test2"
232```
233
234From another dataset
235
236```go
237ds := goqu.From("test")
238fromDs := ds.Where(goqu.C("age").Gt(10))
239sql, _, _ := ds.From(fromDs).ToSQL()
240fmt.Println(sql)
241```
242
243Output:
244```
245SELECT * FROM (SELECT * FROM "test" WHERE ("age" > 10)) AS "t1"
246```
247
248From an aliased dataset
249
250```go
251ds := goqu.From("test")
252fromDs := ds.Where(goqu.C("age").Gt(10))
253sql, _, _ := ds.From(fromDs.As("test2")).ToSQL()
254fmt.Println(sql)
255```
256
257Output:
258```
259SELECT * FROM (SELECT * FROM "test" WHERE ("age" > 10)) AS "test2"
260```
261
262Lateral Query
263
264```go
265maxEntry := goqu.From("entry").
266 Select(goqu.MAX("int").As("max_int")).
267 Where(goqu.Ex{"time": goqu.Op{"lt": goqu.I("e.time")}}).
268 As("max_entry")
269
270maxId := goqu.From("entry").
271 Select("id").
272 Where(goqu.Ex{"int": goqu.I("max_entry.max_int")}).
273 As("max_id")
274
275ds := goqu.
276 Select("e.id", "max_entry.max_int", "max_id.id").
277 From(
278 goqu.T("entry").As("e"),
279 goqu.Lateral(maxEntry),
280 goqu.Lateral(maxId),
281 )
282query, args, _ := ds.ToSQL()
283fmt.Println(query, args)
284
285query, args, _ = ds.Prepared(true).ToSQL()
286fmt.Println(query, args)
287```
288
289Output
290```
291SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e", LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry", LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" []
292SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e", LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry", LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" []
293```
294
295<a name="joins"></a>
296**[`Join`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Join)**
297
298```go
299sql, _, _ := goqu.From("test").Join(
300 goqu.T("test2"),
301 goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")}),
302).ToSQL()
303fmt.Println(sql)
304```
305
306Output:
307```
308SELECT * FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
309```
310
311[`InnerJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.InnerJoin)
312
313```go
314sql, _, _ := goqu.From("test").InnerJoin(
315 goqu.T("test2"),
316 goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")}),
317).ToSQL()
318fmt.Println(sql)
319```
320
321Output:
322```
323SELECT * FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
324```
325
326[`FullOuterJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.FullOuterJoin)
327
328```go
329sql, _, _ := goqu.From("test").FullOuterJoin(
330 goqu.T("test2"),
331 goqu.On(goqu.Ex{
332 "test.fkey": goqu.I("test2.Id"),
333 }),
334).ToSQL()
335fmt.Println(sql)
336```
337
338Output:
339```
340SELECT * FROM "test" FULL OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
341```
342
343[`RightOuterJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.RightOuterJoin)
344
345```go
346sql, _, _ := goqu.From("test").RightOuterJoin(
347 goqu.T("test2"),
348 goqu.On(goqu.Ex{
349 "test.fkey": goqu.I("test2.Id"),
350 }),
351).ToSQL()
352fmt.Println(sql)
353```
354
355Output:
356```
357SELECT * FROM "test" RIGHT OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
358```
359
360[`LeftOuterJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.LeftOuterJoin)
361
362```go
363sql, _, _ := goqu.From("test").LeftOuterJoin(
364 goqu.T("test2"),
365 goqu.On(goqu.Ex{
366 "test.fkey": goqu.I("test2.Id"),
367 }),
368).ToSQL()
369fmt.Println(sql)
370```
371
372Output:
373```
374SELECT * FROM "test" LEFT OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
375```
376
377[`FullJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.FullJoin)
378
379```go
380sql, _, _ := goqu.From("test").FullJoin(
381 goqu.T("test2"),
382 goqu.On(goqu.Ex{
383 "test.fkey": goqu.I("test2.Id"),
384 }),
385).ToSQL()
386fmt.Println(sql)
387```
388
389Output:
390```
391SELECT * FROM "test" FULL JOIN "test2" ON ("test"."fkey" = "test2"."Id")
392```
393
394
395[`RightJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.RightJoin)
396
397```go
398sql, _, _ := goqu.From("test").RightJoin(
399 goqu.T("test2"),
400 goqu.On(goqu.Ex{
401 "test.fkey": goqu.I("test2.Id"),
402 }),
403).ToSQL()
404fmt.Println(sql)
405```
406
407Output:
408```
409SELECT * FROM "test" RIGHT JOIN "test2" ON ("test"."fkey" = "test2"."Id")
410```
411
412[`LeftJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.LeftJoin)
413
414```go
415sql, _, _ := goqu.From("test").LeftJoin(
416 goqu.T("test2"),
417 goqu.On(goqu.Ex{
418 "test.fkey": goqu.I("test2.Id"),
419 }),
420).ToSQL()
421fmt.Println(sql)
422```
423
424Output:
425```
426SELECT * FROM "test" LEFT JOIN "test2" ON ("test"."fkey" = "test2"."Id")
427```
428
429[`NaturalJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.NaturalJoin)
430
431```go
432sql, _, _ := goqu.From("test").NaturalJoin(goqu.T("test2")).ToSQL()
433fmt.Println(sql)
434```
435
436Output:
437```
438SELECT * FROM "test" NATURAL JOIN "test2"
439```
440
441[`NaturalLeftJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.NaturalLeftJoin)
442
443```go
444sql, _, _ := goqu.From("test").NaturalLeftJoin(goqu.T("test2")).ToSQL()
445fmt.Println(sql)
446```
447
448Output:
449```
450SELECT * FROM "test" NATURAL LEFT JOIN "test2"
451```
452
453[`NaturalRightJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.NaturalRightJoin)
454
455```go
456sql, _, _ := goqu.From("test").NaturalRightJoin(goqu.T("test2")).ToSQL()
457fmt.Println(sql)
458```
459
460Output:
461```
462SELECT * FROM "test" NATURAL RIGHT LEFT JOIN "test2"
463```
464
465[`NaturalFullJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.NaturalFullJoin)
466
467```go
468sql, _, _ := goqu.From("test").NaturalFullJoin(goqu.T("test2")).ToSQL()
469fmt.Println(sql)
470```
471
472Output:
473```
474SELECT * FROM "test" NATURAL FULL LEFT JOIN "test2"
475```
476
477[`CrossJoin`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.CrossJoin)
478
479```go
480sql, _, _ := goqu.From("test").CrossJoin(goqu.T("test2")).ToSQL()
481fmt.Println(sql)
482```
483
484Output:
485```
486SELECT * FROM "test" CROSS JOIN "test2"
487```
488
489Join with a Lateral
490
491```go
492maxEntry := goqu.From("entry").
493 Select(goqu.MAX("int").As("max_int")).
494 Where(goqu.Ex{"time": goqu.Op{"lt": goqu.I("e.time")}}).
495 As("max_entry")
496
497maxId := goqu.From("entry").
498 Select("id").
499 Where(goqu.Ex{"int": goqu.I("max_entry.max_int")}).
500 As("max_id")
501
502ds := goqu.
503 Select("e.id", "max_entry.max_int", "max_id.id").
504 From(goqu.T("entry").As("e")).
505 Join(goqu.Lateral(maxEntry), goqu.On(goqu.V(true))).
506 Join(goqu.Lateral(maxId), goqu.On(goqu.V(true)))
507query, args, _ := ds.ToSQL()
508fmt.Println(query, args)
509
510query, args, _ = ds.Prepared(true).ToSQL()
511fmt.Println(query, args)
512```
513
514Output:
515```
516SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e" INNER JOIN LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry" ON TRUE INNER JOIN LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" ON TRUE []
517
518SELECT "e"."id", "max_entry"."max_int", "max_id"."id" FROM "entry" AS "e" INNER JOIN LATERAL (SELECT MAX("int") AS "max_int" FROM "entry" WHERE ("time" < "e"."time")) AS "max_entry" ON ? INNER JOIN LATERAL (SELECT "id" FROM "entry" WHERE ("int" = "max_entry"."max_int")) AS "max_id" ON ? [true true]
519```
520
521<a name="where"></a>
522**[`Where`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Where)**
523
524You can use `goqu.Ex` to create an ANDed condition
525```go
526sql, _, _ := goqu.From("test").Where(goqu.Ex{
527 "a": goqu.Op{"gt": 10},
528 "b": goqu.Op{"lt": 10},
529 "c": nil,
530 "d": []string{"a", "b", "c"},
531}).ToSQL()
532fmt.Println(sql)
533```
534
535Output:
536
537```
538SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
539```
540
541You can use `goqu.ExOr` to create an ORed condition
542
543```go
544sql, _, _ := goqu.From("test").Where(goqu.ExOr{
545 "a": goqu.Op{"gt": 10},
546 "b": goqu.Op{"lt": 10},
547 "c": nil,
548 "d": []string{"a", "b", "c"},
549}).ToSQL()
550fmt.Println(sql)
551```
552
553Output:
554```
555SELECT * FROM "test" WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c')))
556```
557
558You can use `goqu.Ex` with `goqu.ExOr` for complex expressions
559
560```go
561// You can use Or with Ex to Or multiple Ex maps together
562sql, _, _ := goqu.From("test").Where(
563 goqu.Or(
564 goqu.Ex{
565 "a": goqu.Op{"gt": 10},
566 "b": goqu.Op{"lt": 10},
567 },
568 goqu.Ex{
569 "c": nil,
570 "d": []string{"a", "b", "c"},
571 },
572 ),
573).ToSQL()
574fmt.Println(sql)
575```
576
577Output:
578
579```
580SELECT * FROM "test" WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))))
581```
582
583You can also use identifiers to create your where condition
584
585```go
586sql, _, _ := goqu.From("test").Where(
587 goqu.C("a").Gt(10),
588 goqu.C("b").Lt(10),
589 goqu.C("c").IsNull(),
590 goqu.C("d").In("a", "b", "c"),
591).ToSQL()
592fmt.Println(sql)
593```
594
595Output:
596```
597SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
598```
599
600Using `goqu.Or` to create ORed expression
601
602```go
603// You can use a combination of Ors and Ands
604sql, _, _ := goqu.From("test").Where(
605 goqu.Or(
606 goqu.C("a").Gt(10),
607 goqu.And(
608 goqu.C("b").Lt(10),
609 goqu.C("c").IsNull(),
610 ),
611 ),
612).ToSQL()
613fmt.Println(sql)
614```
615
616Output:
617
618```
619SELECT * FROM "test" WHERE (("a" > 10) OR (("b" < 10) AND ("c" IS NULL)))
620```
621
622<a name="limit"></a>
623**[`Limit`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Limit)**
624
625```go
626ds := goqu.From("test").Limit(10)
627sql, _, _ := ds.ToSQL()
628fmt.Println(sql)
629```
630
631Output:
632
633```
634SELECT * FROM "test" LIMIT 10
635```
636
637<a name="offset"></a>
638**[`Offset`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Offset)**
639
640```go
641ds := goqu.From("test").Offset(2)
642sql, _, _ := ds.ToSQL()
643fmt.Println(sql)
644```
645
646Output:
647
648```
649SELECT * FROM "test" OFFSET 2
650```
651
652<a name="group_by"></a>
653**[`GroupBy`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.GroupBy)**
654
655```go
656sql, _, _ := goqu.From("test").
657 Select(goqu.SUM("income").As("income_sum")).
658 GroupBy("age").
659 ToSQL()
660fmt.Println(sql)
661```
662
663Output:
664
665```
666SELECT SUM("income") AS "income_sum" FROM "test" GROUP BY "age"
667```
668
669<a name="having"></a>
670**[`Having`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Having)**
671
672```go
673sql, _, _ = goqu.From("test").GroupBy("age").Having(goqu.SUM("income").Gt(1000)).ToSQL()
674fmt.Println(sql)
675```
676
677Output:
678
679```
680SELECT * FROM "test" GROUP BY "age" HAVING (SUM("income") > 1000)
681```
682
683<a name="with"></a>
684**[`With`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.With)**
685
686To use CTEs in `SELECT` statements you can use the `With` method.
687
688Simple Example
689
690```go
691sql, _, _ := goqu.From("one").
692 With("one", goqu.From().Select(goqu.L("1"))).
693 Select(goqu.Star()).
694 ToSQL()
695fmt.Println(sql)
696```
697
698Output:
699
700```
701WITH one AS (SELECT 1) SELECT * FROM "one"
702```
703
704Dependent `WITH` clauses:
705
706```go
707sql, _, _ = goqu.From("derived").
708 With("intermed", goqu.From("test").Select(goqu.Star()).Where(goqu.C("x").Gte(5))).
709 With("derived", goqu.From("intermed").Select(goqu.Star()).Where(goqu.C("x").Lt(10))).
710 Select(goqu.Star()).
711 ToSQL()
712fmt.Println(sql)
713```
714
715Output:
716```
717WITH intermed AS (SELECT * FROM "test" WHERE ("x" >= 5)), derived AS (SELECT * FROM "intermed" WHERE ("x" < 10)) SELECT * FROM "derived"
718```
719
720`WITH` clause with arguments
721
722```go
723sql, _, _ = goqu.From("multi").
724 With("multi(x,y)", goqu.From().Select(goqu.L("1"), goqu.L("2"))).
725 Select(goqu.C("x"), goqu.C("y")).
726 ToSQL()
727fmt.Println(sql)
728```
729
730Output:
731```
732WITH multi(x,y) AS (SELECT 1, 2) SELECT "x", "y" FROM "multi"
733```
734
735Using a `InsertDataset`.
736
737```go
738insertDs := goqu.Insert("foo").Rows(goqu.Record{"user_id": 10}).Returning("id")
739
740ds := goqu.From("bar").
741 With("ins", insertDs).
742 Select("bar_name").
743 Where(goqu.Ex{"bar.user_id": goqu.I("ins.user_id")})
744
745sql, _, _ := ds.ToSQL()
746fmt.Println(sql)
747
748sql, args, _ := ds.Prepared(true).ToSQL()
749fmt.Println(sql, args)
750```
751Output:
752```
753WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (10) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id")
754WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (?) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id") [10]
755```
756
757Using an `UpdateDataset`
758
759```go
760updateDs := goqu.Update("foo").Set(goqu.Record{"bar": "baz"}).Returning("id")
761
762ds := goqu.From("bar").
763 With("upd", updateDs).
764 Select("bar_name").
765 Where(goqu.Ex{"bar.user_id": goqu.I("ins.user_id")})
766
767sql, _, _ := ds.ToSQL()
768fmt.Println(sql)
769
770sql, args, _ := ds.Prepared(true).ToSQL()
771fmt.Println(sql, args)
772```
773
774Output:
775```
776WITH upd AS (UPDATE "foo" SET "bar"='baz' RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "upd"."user_id")
777WITH upd AS (UPDATE "foo" SET "bar"=? RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "upd"."user_id") [baz]
778```
779
780Using a `DeleteDataset`
781
782```go
783deleteDs := goqu.Delete("foo").Where(goqu.Ex{"bar": "baz"}).Returning("id")
784
785ds := goqu.From("bar").
786 With("del", deleteDs).
787 Select("bar_name").
788 Where(goqu.Ex{"bar.user_id": goqu.I("del.user_id")})
789
790sql, _, _ := ds.ToSQL()
791fmt.Println(sql)
792
793sql, args, _ := ds.Prepared(true).ToSQL()
794fmt.Println(sql, args)
795```
796
797Output:
798```
799WITH del AS (DELETE FROM "foo" WHERE ("bar" = 'baz') RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id")
800WITH del AS (DELETE FROM "foo" WHERE ("bar" = ?) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id") [baz]
801```
802
803<a name="window"></a>
804**[`Window Function`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Window)**
805
806**NOTE** currently only the `postgres`, `mysql8` (NOT `mysql`) and the default dialect support `Window Function`
807
808To use windowing in `SELECT` statements you can use the `Over` method on an `SQLFunction`
809
810```go
811sql, _, _ := goqu.From("test").Select(
812 goqu.ROW_NUMBER().Over(goqu.W().PartitionBy("a").OrderBy(goqu.I("b").Asc())),
813)
814fmt.Println(sql)
815```
816
817Output:
818
819```
820SELECT ROW_NUMBER() OVER (PARTITION BY "a" ORDER BY "b") FROM "test"
821```
822
823`goqu` also supports the `WINDOW` clause.
824
825```go
826sql, _, _ := goqu.From("test").
827 Select(goqu.ROW_NUMBER().OverName(goqu.I("w"))).
828 Window(goqu.W("w").PartitionBy("a").OrderBy(goqu.I("b").Asc()))
829fmt.Println(sql)
830```
831
832Output:
833
834```
835SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w" AS (PARTITION BY "a" ORDER BY "b")
836```
837
838<a name="seterror"></a>
839**[`SetError`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.SetError)**
840
841Sometimes while building up a query with goqu you will encounter situations where certain
842preconditions are not met or some end-user contraint has been violated. While you could
843track this error case separately, goqu provides a convenient built-in mechanism to set an
844error on a dataset if one has not already been set to simplify query building.
845
846Set an Error on a dataset:
847
848```go
849func GetSelect(name string) *goqu.SelectDataset {
850
851 var ds = goqu.From("test")
852
853 if len(name) == 0 {
854 return ds.SetError(fmt.Errorf("name is empty"))
855 }
856
857 return ds.Select(name)
858}
859
860```
861
862This error is returned on any subsequent call to `Error` or `ToSQL`:
863
864```go
865var name string = ""
866ds = GetSelect(name)
867fmt.Println(ds.Error())
868
869sql, args, err = ds.ToSQL()
870fmt.Println(err)
871```
872
873Output:
874```
875name is empty
876name is empty
877```
878
879<a name="forupdate"></a>
880**[`ForUpdate`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.ForUpdate)**
881
882```go
883sql, _, _ := goqu.From("test").ForUpdate(exp.Wait).ToSQL()
884fmt.Println(sql)
885```
886
887Output:
888```sql
889SELECT * FROM "test" FOR UPDATE
890```
891
892If your dialect supports FOR UPDATE OF you provide tables to be locked as variable arguments to the ForUpdate method.
893
894```go
895sql, _, _ := goqu.From("test").ForUpdate(exp.Wait, goqu.T("test")).ToSQL()
896fmt.Println(sql)
897```
898
899Output:
900```sql
901SELECT * FROM "test" FOR UPDATE OF "test"
902```
903
904## Executing Queries
905
906To execute your query use [`goqu.Database#From`](https://godoc.org/github.com/doug-martin/goqu/#Database.From) to create your dataset
907
908<a name="scan-structs"></a>
909**[`ScanStructs`](http://godoc.org/github.com/doug-martin/goqu#SelectDataset.ScanStructs)**
910
911Scans rows into a slice of structs
912
913**NOTE** [`ScanStructs`](http://godoc.org/github.com/doug-martin/goqu#SelectDataset.ScanStructs) will only select the columns that can be scanned in to the structs unless you have explicitly selected certain columns.
914
915 ```go
916type User struct{
917 FirstName string `db:"first_name"`
918 LastName string `db:"last_name"`
919 Age int `db:"-"` // a field that shouldn't be selected
920}
921
922var users []User
923//SELECT "first_name", "last_name" FROM "user";
924if err := db.From("user").ScanStructs(&users); err != nil{
925 panic(err.Error())
926}
927fmt.Printf("\n%+v", users)
928
929var users []User
930//SELECT "first_name" FROM "user";
931if err := db.From("user").Select("first_name").ScanStructs(&users); err != nil{
932 panic(err.Error())
933}
934fmt.Printf("\n%+v", users)
935```
936
937`goqu` also supports scanning into multiple structs. In the example below we define a `Role` and `User` struct that could both be used individually to scan into. However, you can also create a new struct that adds both structs as fields that can be populated in a single query.
938
939**NOTE** When calling `ScanStructs` without a select already defined it will automatically only `SELECT` the columns found in the struct, omitting any that are tagged with `db:"-"`
940
941 ```go
942type Role struct {
943 Id uint64 `db:"id"`
944 UserID uint64 `db:"user_id"`
945 Name string `db:"name"`
946}
947type User struct {
948 Id uint64 `db:"id"`
949 FirstName string `db:"first_name"`
950 LastName string `db:"last_name"`
951}
952type UserAndRole struct {
953 User User `db:"goqu_user"` // tag as the "goqu_user" table
954 Role Role `db:"user_role"` // tag as "user_role" table
955}
956db := getDb()
957
958ds := db.
959 From("goqu_user").
960 Join(goqu.T("user_role"), goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))))
961var users []UserAndRole
962 // Scan structs will auto build the
963if err := ds.ScanStructs(&users); err != nil {
964 fmt.Println(err.Error())
965 return
966}
967for _, u := range users {
968 fmt.Printf("\n%+v", u)
969}
970```
971
972You can alternatively manually select the columns with the appropriate aliases using the `goqu.C` method to create the alias.
973
974```go
975type Role struct {
976 UserID uint64 `db:"user_id"`
977 Name string `db:"name"`
978}
979type User struct {
980 Id uint64 `db:"id"`
981 FirstName string `db:"first_name"`
982 LastName string `db:"last_name"`
983 Role Role `db:"user_role"` // tag as "user_role" table
984}
985db := getDb()
986
987ds := db.
988 Select(
989 "goqu_user.id",
990 "goqu_user.first_name",
991 "goqu_user.last_name",
992 // alias the fully qualified identifier `C` is important here so it doesnt parse it
993 goqu.I("user_role.user_id").As(goqu.C("user_role.user_id")),
994 goqu.I("user_role.name").As(goqu.C("user_role.name")),
995 ).
996 From("goqu_user").
997 Join(goqu.T("user_role"), goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))))
998
999var users []User
1000if err := ds.ScanStructs(&users); err != nil {
1001 fmt.Println(err.Error())
1002 return
1003}
1004for _, u := range users {
1005 fmt.Printf("\n%+v", u)
1006}
1007```
1008
1009<a name="scan-struct"></a>
1010**[`ScanStruct`](http://godoc.org/github.com/doug-martin/goqu#SelectDataset.ScanStruct)**
1011
1012Scans a row into a slice a struct, returns false if a row wasnt found
1013
1014**NOTE** [`ScanStruct`](http://godoc.org/github.com/doug-martin/goqu#SelectDataset.ScanStruct) will only select the columns that can be scanned in to the struct unless you have explicitly selected certain columns.
1015
1016```go
1017type User struct{
1018 FirstName string `db:"first_name"`
1019 LastName string `db:"last_name"`
1020 Age int `db:"-"` // a field that shouldn't be selected
1021}
1022
1023var user User
1024// SELECT "first_name", "last_name" FROM "user" LIMIT 1;
1025found, err := db.From("user").ScanStruct(&user)
1026if err != nil{
1027 fmt.Println(err.Error())
1028 return
1029}
1030if !found {
1031 fmt.Println("No user found")
1032} else {
1033 fmt.Printf("\nFound user: %+v", user)
1034}
1035```
1036
1037`goqu` also supports scanning into multiple structs. In the example below we define a `Role` and `User` struct that could both be used individually to scan into. However, you can also create a new struct that adds both structs as fields that can be populated in a single query.
1038
1039**NOTE** When calling `ScanStruct` without a select already defined it will automatically only `SELECT` the columns found in the struct, omitting any that are tagged with `db:"-"`
1040
1041 ```go
1042type Role struct {
1043 UserID uint64 `db:"user_id"`
1044 Name string `db:"name"`
1045}
1046type User struct {
1047 ID uint64 `db:"id"`
1048 FirstName string `db:"first_name"`
1049 LastName string `db:"last_name"`
1050}
1051type UserAndRole struct {
1052 User User `db:"goqu_user"` // tag as the "goqu_user" table
1053 Role Role `db:"user_role"` // tag as "user_role" table
1054}
1055db := getDb()
1056var userAndRole UserAndRole
1057ds := db.
1058 From("goqu_user").
1059 Join(goqu.T("user_role"),goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id")))).
1060 Where(goqu.C("first_name").Eq("Bob"))
1061
1062found, err := ds.ScanStruct(&userAndRole)
1063if err != nil{
1064 fmt.Println(err.Error())
1065 return
1066}
1067if !found {
1068 fmt.Println("No user found")
1069} else {
1070 fmt.Printf("\nFound user: %+v", user)
1071}
1072```
1073
1074You can alternatively manually select the columns with the appropriate aliases using the `goqu.C` method to create the alias.
1075
1076```go
1077type Role struct {
1078 UserID uint64 `db:"user_id"`
1079 Name string `db:"name"`
1080}
1081type User struct {
1082 ID uint64 `db:"id"`
1083 FirstName string `db:"first_name"`
1084 LastName string `db:"last_name"`
1085 Role Role `db:"user_role"` // tag as "user_role" table
1086}
1087db := getDb()
1088var userAndRole UserAndRole
1089ds := db.
1090 Select(
1091 "goqu_user.id",
1092 "goqu_user.first_name",
1093 "goqu_user.last_name",
1094 // alias the fully qualified identifier `C` is important here so it doesnt parse it
1095 goqu.I("user_role.user_id").As(goqu.C("user_role.user_id")),
1096 goqu.I("user_role.name").As(goqu.C("user_role.name")),
1097 ).
1098 From("goqu_user").
1099 Join(goqu.T("user_role"),goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id")))).
1100 Where(goqu.C("first_name").Eq("Bob"))
1101
1102found, err := ds.ScanStruct(&userAndRole)
1103if err != nil{
1104 fmt.Println(err.Error())
1105 return
1106}
1107if !found {
1108 fmt.Println("No user found")
1109} else {
1110 fmt.Printf("\nFound user: %+v", user)
1111}
1112```
1113
1114
1115**NOTE** Using the `goqu.SetColumnRenameFunction` function, you can change the function that's used to rename struct fields when struct tags aren't defined
1116
1117```go
1118import "strings"
1119
1120goqu.SetColumnRenameFunction(strings.ToUpper)
1121
1122type User struct{
1123 FirstName string
1124 LastName string
1125}
1126
1127var user User
1128//SELECT "FIRSTNAME", "LASTNAME" FROM "user" LIMIT 1;
1129found, err := db.From("user").ScanStruct(&user)
1130// ...
1131```
1132
1133**NOTE** Using the `goqu.SetIgnoreUntaggedFields(true)` function, you can cause goqu to ignore any fields that aren't explicitly tagged.
1134
1135```go
1136goqu.SetIgnoreUntaggedFields(true)
1137
1138type User struct{
1139 FirstName string `db:"first_name"`
1140 LastName string
1141}
1142
1143var user User
1144//SELECT "first_name" FROM "user" LIMIT 1;
1145found, err := db.From("user").ScanStruct(&user)
1146// ...
1147```
1148
1149
1150<a name="scan-vals"></a>
1151**[`ScanVals`](http://godoc.org/github.com/doug-martin/goqu#SelectDataset.ScanVals)**
1152
1153Scans a rows of 1 column into a slice of primitive values
1154
1155```go
1156var ids []int64
1157if err := db.From("user").Select("id").ScanVals(&ids); err != nil{
1158 fmt.Println(err.Error())
1159 return
1160}
1161fmt.Printf("\n%+v", ids)
1162```
1163
1164<a name="scan-val"></a>
1165[`ScanVal`](http://godoc.org/github.com/doug-martin/goqu#SelectDataset.ScanVal)
1166
1167Scans a row of 1 column into a primitive value, returns false if a row wasnt found.
1168
1169**Note** when using the dataset a `LIMIT` of 1 is automatically applied.
1170```go
1171var id int64
1172found, err := db.From("user").Select("id").ScanVal(&id)
1173if err != nil{
1174 fmt.Println(err.Error())
1175 return
1176}
1177if !found{
1178 fmt.Println("No id found")
1179}else{
1180 fmt.Printf("\nFound id: %d", id)
1181}
1182```
1183
1184<a name="scanner"></a>
1185**[`Scanner`](http://godoc.org/github.com/doug-martin/goqu/exec#Scanner)**
1186
1187Scanner knows how to scan rows into structs. This is useful when dealing with large result sets where you can have only one item scanned in memory at one time.
1188
1189In the following example we scan each row into struct.
1190
1191```go
1192
1193type User struct {
1194 FirstName string `db:"first_name"`
1195 LastName string `db:"last_name"`
1196}
1197db := getDb()
1198
1199scanner, err := db.
1200 From("goqu_user").
1201 Select("first_name", "last_name").
1202 Where(goqu.Ex{
1203 "last_name": "Yukon",
1204 }).
1205 Executor().
1206 Scanner()
1207
1208if err != nil {
1209 fmt.Println(err.Error())
1210 return
1211}
1212
1213defer scanner.Close()
1214
1215for scanner.Next() {
1216 u := User{}
1217
1218 err = scanner.ScanStruct(&u)
1219 if err != nil {
1220 fmt.Println(err.Error())
1221 return
1222 }
1223
1224 fmt.Printf("\n%+v", u)
1225}
1226
1227if scanner.Err() != nil {
1228 fmt.Println(scanner.Err().Error())
1229}
1230```
1231
1232In this example we scan each row into a val.
1233```go
1234db := getDb()
1235
1236scanner, err := db.
1237 From("goqu_user").
1238 Select("first_name").
1239 Where(goqu.Ex{
1240 "last_name": "Yukon",
1241 }).
1242 Executor().
1243 Scanner()
1244
1245if err != nil {
1246 fmt.Println(err.Error())
1247 return
1248}
1249
1250defer scanner.Close()
1251
1252for scanner.Next() {
1253 name := ""
1254
1255 err = scanner.ScanVal(&name)
1256 if err != nil {
1257 fmt.Println(err.Error())
1258 return
1259 }
1260
1261 fmt.Println(name)
1262}
1263
1264if scanner.Err() != nil {
1265 fmt.Println(scanner.Err().Error())
1266}
1267```
1268
1269<a name="count"></a>
1270**[`Count`](http://godoc.org/github.com/doug-martin/goqu#SelectDataset.Count)**
1271
1272Returns the count for the current query
1273
1274```go
1275count, err := db.From("user").Count()
1276if err != nil{
1277 fmt.Println(err.Error())
1278 return
1279}
1280fmt.Printf("\nCount:= %d", count)
1281```
1282
1283<a name="pluck"></a>
1284**[`Pluck`](http://godoc.org/github.com/doug-martin/goqu#SelectDataset.Pluck)**
1285
1286Selects a single column and stores the results into a slice of primitive values
1287
1288```go
1289var ids []int64
1290if err := db.From("user").Pluck(&ids, "id"); err != nil{
1291 fmt.Println(err.Error())
1292 return
1293}
1294fmt.Printf("\nIds := %+v", ids)
1295```
View as plain text