Source file src/github.com/doug-martin/goqu/v9/select_dataset_example_test.go

Documentation: github.com/doug-martin/goqu/v9

     1  // nolint:lll // sql statements are long
     2  package goqu_test
     3  
     4  import (
     5  	goSQL "database/sql"
     6  	"fmt"
     7  	"os"
     8  	"regexp"
     9  	"time"
    10  
    11  	"github.com/doug-martin/goqu/v9"
    12  	"github.com/doug-martin/goqu/v9/exp"
    13  	"github.com/lib/pq"
    14  )
    15  
    16  const schema = `
    17  		DROP TABLE IF EXISTS "user_role";
    18  		DROP TABLE IF EXISTS "goqu_user";	
    19  		CREATE  TABLE "goqu_user" (
    20  			"id" SERIAL PRIMARY KEY NOT NULL,
    21  			"first_name" VARCHAR(45) NOT NULL,
    22  			"last_name" VARCHAR(45) NOT NULL,
    23  			"created" TIMESTAMP NOT NULL DEFAULT now()
    24  		);
    25  		CREATE  TABLE "user_role" (
    26  			"id" SERIAL PRIMARY KEY NOT NULL,
    27  			"user_id" BIGINT NOT NULL REFERENCES goqu_user(id) ON DELETE CASCADE,
    28  			"name" VARCHAR(45) NOT NULL,
    29  			"created" TIMESTAMP NOT NULL DEFAULT now()
    30  		); 
    31      `
    32  
    33  const defaultDBURI = "postgres://postgres:@localhost:5435/goqupostgres?sslmode=disable"
    34  
    35  var goquDB *goqu.Database
    36  
    37  func getDB() *goqu.Database {
    38  	if goquDB == nil {
    39  		dbURI := os.Getenv("PG_URI")
    40  		if dbURI == "" {
    41  			dbURI = defaultDBURI
    42  		}
    43  		uri, err := pq.ParseURL(dbURI)
    44  		if err != nil {
    45  			panic(err)
    46  		}
    47  		pdb, err := goSQL.Open("postgres", uri)
    48  		if err != nil {
    49  			panic(err)
    50  		}
    51  		goquDB = goqu.New("postgres", pdb)
    52  	}
    53  	// reset the db
    54  	if _, err := goquDB.Exec(schema); err != nil {
    55  		panic(err)
    56  	}
    57  	type goquUser struct {
    58  		ID        int64     `db:"id" goqu:"skipinsert"`
    59  		FirstName string    `db:"first_name"`
    60  		LastName  string    `db:"last_name"`
    61  		Created   time.Time `db:"created" goqu:"skipupdate"`
    62  	}
    63  
    64  	users := []goquUser{
    65  		{FirstName: "Bob", LastName: "Yukon"},
    66  		{FirstName: "Sally", LastName: "Yukon"},
    67  		{FirstName: "Vinita", LastName: "Yukon"},
    68  		{FirstName: "John", LastName: "Doe"},
    69  	}
    70  	var userIds []int64
    71  	err := goquDB.Insert("goqu_user").Rows(users).Returning("id").Executor().ScanVals(&userIds)
    72  	if err != nil {
    73  		panic(err)
    74  	}
    75  	type userRole struct {
    76  		ID      int64     `db:"id" goqu:"skipinsert"`
    77  		UserID  int64     `db:"user_id"`
    78  		Name    string    `db:"name"`
    79  		Created time.Time `db:"created" goqu:"skipupdate"`
    80  	}
    81  
    82  	roles := []userRole{
    83  		{UserID: userIds[0], Name: "Admin"},
    84  		{UserID: userIds[1], Name: "Manager"},
    85  		{UserID: userIds[2], Name: "Manager"},
    86  		{UserID: userIds[3], Name: "User"},
    87  	}
    88  	_, err = goquDB.Insert("user_role").Rows(roles).Executor().Exec()
    89  	if err != nil {
    90  		panic(err)
    91  	}
    92  	return goquDB
    93  }
    94  
    95  func ExampleSelectDataset() {
    96  	ds := goqu.From("test").
    97  		Select(goqu.COUNT("*")).
    98  		InnerJoin(goqu.T("test2"), goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.id")))).
    99  		LeftJoin(goqu.T("test3"), goqu.On(goqu.I("test2.fkey").Eq(goqu.I("test3.id")))).
   100  		Where(
   101  			goqu.Ex{
   102  				"test.name": goqu.Op{
   103  					"like": regexp.MustCompile("^[ab]"),
   104  				},
   105  				"test2.amount": goqu.Op{
   106  					"isNot": nil,
   107  				},
   108  			},
   109  			goqu.ExOr{
   110  				"test3.id":     nil,
   111  				"test3.status": []string{"passed", "active", "registered"},
   112  			}).
   113  		Order(goqu.I("test.created").Desc().NullsLast()).
   114  		GroupBy(goqu.I("test.user_id")).
   115  		Having(goqu.AVG("test3.age").Gt(10))
   116  
   117  	sql, args, _ := ds.ToSQL()
   118  	fmt.Println(sql, args)
   119  
   120  	sql, args, _ = ds.Prepared(true).ToSQL()
   121  	fmt.Println(sql, args)
   122  	// nolint:lll // SQL statements are long
   123  	// Output:
   124  	// SELECT COUNT(*) FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."id") LEFT JOIN "test3" ON ("test2"."fkey" = "test3"."id") WHERE ((("test"."name" ~ '^[ab]') AND ("test2"."amount" IS NOT NULL)) AND (("test3"."id" IS NULL) OR ("test3"."status" IN ('passed', 'active', 'registered')))) GROUP BY "test"."user_id" HAVING (AVG("test3"."age") > 10) ORDER BY "test"."created" DESC NULLS LAST []
   125  	// SELECT COUNT(*) FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."id") LEFT JOIN "test3" ON ("test2"."fkey" = "test3"."id") WHERE ((("test"."name" ~ ?) AND ("test2"."amount" IS NOT NULL)) AND (("test3"."id" IS NULL) OR ("test3"."status" IN (?, ?, ?)))) GROUP BY "test"."user_id" HAVING (AVG("test3"."age") > ?) ORDER BY "test"."created" DESC NULLS LAST [^[ab] passed active registered 10]
   126  }
   127  
   128  func ExampleSelect() {
   129  	sql, _, _ := goqu.Select(goqu.L("NOW()")).ToSQL()
   130  	fmt.Println(sql)
   131  
   132  	// Output:
   133  	// SELECT NOW()
   134  }
   135  
   136  func ExampleFrom() {
   137  	sql, args, _ := goqu.From("test").ToSQL()
   138  	fmt.Println(sql, args)
   139  
   140  	// Output:
   141  	// SELECT * FROM "test" []
   142  }
   143  
   144  func ExampleSelectDataset_As() {
   145  	ds := goqu.From("test").As("t")
   146  	sql, _, _ := goqu.From(ds).ToSQL()
   147  	fmt.Println(sql)
   148  	// Output: SELECT * FROM (SELECT * FROM "test") AS "t"
   149  }
   150  
   151  func ExampleSelectDataset_Union() {
   152  	sql, _, _ := goqu.From("test").
   153  		Union(goqu.From("test2")).
   154  		ToSQL()
   155  	fmt.Println(sql)
   156  
   157  	sql, _, _ = goqu.From("test").
   158  		Limit(1).
   159  		Union(goqu.From("test2")).
   160  		ToSQL()
   161  	fmt.Println(sql)
   162  
   163  	sql, _, _ = goqu.From("test").
   164  		Limit(1).
   165  		Union(goqu.From("test2").
   166  			Order(goqu.C("id").Desc())).
   167  		ToSQL()
   168  	fmt.Println(sql)
   169  	// Output:
   170  	// SELECT * FROM "test" UNION (SELECT * FROM "test2")
   171  	// SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" UNION (SELECT * FROM "test2")
   172  	// SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" UNION (SELECT * FROM (SELECT * FROM "test2" ORDER BY "id" DESC) AS "t1")
   173  }
   174  
   175  func ExampleSelectDataset_UnionAll() {
   176  	sql, _, _ := goqu.From("test").
   177  		UnionAll(goqu.From("test2")).
   178  		ToSQL()
   179  	fmt.Println(sql)
   180  	sql, _, _ = goqu.From("test").
   181  		Limit(1).
   182  		UnionAll(goqu.From("test2")).
   183  		ToSQL()
   184  	fmt.Println(sql)
   185  	sql, _, _ = goqu.From("test").
   186  		Limit(1).
   187  		UnionAll(goqu.From("test2").
   188  			Order(goqu.C("id").Desc())).
   189  		ToSQL()
   190  	fmt.Println(sql)
   191  	// Output:
   192  	// SELECT * FROM "test" UNION ALL (SELECT * FROM "test2")
   193  	// SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" UNION ALL (SELECT * FROM "test2")
   194  	// SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" UNION ALL (SELECT * FROM (SELECT * FROM "test2" ORDER BY "id" DESC) AS "t1")
   195  }
   196  
   197  func ExampleSelectDataset_With() {
   198  	sql, _, _ := goqu.From("one").
   199  		With("one", goqu.From().Select(goqu.L("1"))).
   200  		Select(goqu.Star()).
   201  		ToSQL()
   202  	fmt.Println(sql)
   203  	sql, _, _ = goqu.From("derived").
   204  		With("intermed", goqu.From("test").Select(goqu.Star()).Where(goqu.C("x").Gte(5))).
   205  		With("derived", goqu.From("intermed").Select(goqu.Star()).Where(goqu.C("x").Lt(10))).
   206  		Select(goqu.Star()).
   207  		ToSQL()
   208  	fmt.Println(sql)
   209  	sql, _, _ = goqu.From("multi").
   210  		With("multi(x,y)", goqu.From().Select(goqu.L("1"), goqu.L("2"))).
   211  		Select(goqu.C("x"), goqu.C("y")).
   212  		ToSQL()
   213  	fmt.Println(sql)
   214  
   215  	// Output:
   216  	// WITH one AS (SELECT 1) SELECT * FROM "one"
   217  	// WITH intermed AS (SELECT * FROM "test" WHERE ("x" >= 5)), derived AS (SELECT * FROM "intermed" WHERE ("x" < 10)) SELECT * FROM "derived"
   218  	// WITH multi(x,y) AS (SELECT 1, 2) SELECT "x", "y" FROM "multi"
   219  }
   220  
   221  func ExampleSelectDataset_With_insertDataset() {
   222  	insertDs := goqu.Insert("foo").Rows(goqu.Record{"user_id": 10}).Returning("id")
   223  
   224  	ds := goqu.From("bar").
   225  		With("ins", insertDs).
   226  		Select("bar_name").
   227  		Where(goqu.Ex{"bar.user_id": goqu.I("ins.user_id")})
   228  
   229  	sql, _, _ := ds.ToSQL()
   230  	fmt.Println(sql)
   231  
   232  	sql, args, _ := ds.Prepared(true).ToSQL()
   233  	fmt.Println(sql, args)
   234  
   235  	// Output:
   236  	// WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (10) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id")
   237  	// WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (?) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id") [10]
   238  }
   239  
   240  func ExampleSelectDataset_With_updateDataset() {
   241  	updateDs := goqu.Update("foo").Set(goqu.Record{"bar": "baz"}).Returning("id")
   242  
   243  	ds := goqu.From("bar").
   244  		With("upd", updateDs).
   245  		Select("bar_name").
   246  		Where(goqu.Ex{"bar.user_id": goqu.I("upd.user_id")})
   247  
   248  	sql, _, _ := ds.ToSQL()
   249  	fmt.Println(sql)
   250  
   251  	sql, args, _ := ds.Prepared(true).ToSQL()
   252  	fmt.Println(sql, args)
   253  
   254  	// Output:
   255  	// WITH upd AS (UPDATE "foo" SET "bar"='baz' RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "upd"."user_id")
   256  	// WITH upd AS (UPDATE "foo" SET "bar"=? RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "upd"."user_id") [baz]
   257  }
   258  
   259  func ExampleSelectDataset_With_deleteDataset() {
   260  	deleteDs := goqu.Delete("foo").Where(goqu.Ex{"bar": "baz"}).Returning("id")
   261  
   262  	ds := goqu.From("bar").
   263  		With("del", deleteDs).
   264  		Select("bar_name").
   265  		Where(goqu.Ex{"bar.user_id": goqu.I("del.user_id")})
   266  
   267  	sql, _, _ := ds.ToSQL()
   268  	fmt.Println(sql)
   269  
   270  	sql, args, _ := ds.Prepared(true).ToSQL()
   271  	fmt.Println(sql, args)
   272  	// Output:
   273  	// WITH del AS (DELETE FROM "foo" WHERE ("bar" = 'baz') RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id")
   274  	// WITH del AS (DELETE FROM "foo" WHERE ("bar" = ?) RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id") [baz]
   275  }
   276  
   277  func ExampleSelectDataset_WithRecursive() {
   278  	sql, _, _ := goqu.From("nums").
   279  		WithRecursive("nums(x)",
   280  			goqu.From().Select(goqu.L("1")).
   281  				UnionAll(goqu.From("nums").
   282  					Select(goqu.L("x+1")).Where(goqu.C("x").Lt(5)))).
   283  		ToSQL()
   284  	fmt.Println(sql)
   285  	// Output:
   286  	// WITH RECURSIVE nums(x) AS (SELECT 1 UNION ALL (SELECT x+1 FROM "nums" WHERE ("x" < 5))) SELECT * FROM "nums"
   287  }
   288  
   289  func ExampleSelectDataset_Intersect() {
   290  	sql, _, _ := goqu.From("test").
   291  		Intersect(goqu.From("test2")).
   292  		ToSQL()
   293  	fmt.Println(sql)
   294  	sql, _, _ = goqu.From("test").
   295  		Limit(1).
   296  		Intersect(goqu.From("test2")).
   297  		ToSQL()
   298  	fmt.Println(sql)
   299  	sql, _, _ = goqu.From("test").
   300  		Limit(1).
   301  		Intersect(goqu.From("test2").
   302  			Order(goqu.C("id").Desc())).
   303  		ToSQL()
   304  	fmt.Println(sql)
   305  	// Output:
   306  	// SELECT * FROM "test" INTERSECT (SELECT * FROM "test2")
   307  	// SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" INTERSECT (SELECT * FROM "test2")
   308  	// SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" INTERSECT (SELECT * FROM (SELECT * FROM "test2" ORDER BY "id" DESC) AS "t1")
   309  }
   310  
   311  func ExampleSelectDataset_IntersectAll() {
   312  	sql, _, _ := goqu.From("test").
   313  		IntersectAll(goqu.From("test2")).
   314  		ToSQL()
   315  	fmt.Println(sql)
   316  	sql, _, _ = goqu.From("test").
   317  		Limit(1).
   318  		IntersectAll(goqu.From("test2")).
   319  		ToSQL()
   320  	fmt.Println(sql)
   321  	sql, _, _ = goqu.From("test").
   322  		Limit(1).
   323  		IntersectAll(goqu.From("test2").
   324  			Order(goqu.C("id").Desc())).
   325  		ToSQL()
   326  	fmt.Println(sql)
   327  	// Output:
   328  	// SELECT * FROM "test" INTERSECT ALL (SELECT * FROM "test2")
   329  	// SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" INTERSECT ALL (SELECT * FROM "test2")
   330  	// SELECT * FROM (SELECT * FROM "test" LIMIT 1) AS "t1" INTERSECT ALL (SELECT * FROM (SELECT * FROM "test2" ORDER BY "id" DESC) AS "t1")
   331  }
   332  
   333  func ExampleSelectDataset_ClearOffset() {
   334  	ds := goqu.From("test").
   335  		Offset(2)
   336  	sql, _, _ := ds.
   337  		ClearOffset().
   338  		ToSQL()
   339  	fmt.Println(sql)
   340  	// Output:
   341  	// SELECT * FROM "test"
   342  }
   343  
   344  func ExampleSelectDataset_Offset() {
   345  	ds := goqu.From("test").Offset(2)
   346  	sql, _, _ := ds.ToSQL()
   347  	fmt.Println(sql)
   348  	// Output:
   349  	// SELECT * FROM "test" OFFSET 2
   350  }
   351  
   352  func ExampleSelectDataset_Limit() {
   353  	ds := goqu.From("test").Limit(10)
   354  	sql, _, _ := ds.ToSQL()
   355  	fmt.Println(sql)
   356  	// Output:
   357  	// SELECT * FROM "test" LIMIT 10
   358  }
   359  
   360  func ExampleSelectDataset_LimitAll() {
   361  	ds := goqu.From("test").LimitAll()
   362  	sql, _, _ := ds.ToSQL()
   363  	fmt.Println(sql)
   364  	// Output:
   365  	// SELECT * FROM "test" LIMIT ALL
   366  }
   367  
   368  func ExampleSelectDataset_ClearLimit() {
   369  	ds := goqu.From("test").Limit(10)
   370  	sql, _, _ := ds.ClearLimit().ToSQL()
   371  	fmt.Println(sql)
   372  	// Output:
   373  	// SELECT * FROM "test"
   374  }
   375  
   376  func ExampleSelectDataset_Order() {
   377  	ds := goqu.From("test").Order(goqu.C("a").Asc())
   378  	sql, _, _ := ds.ToSQL()
   379  	fmt.Println(sql)
   380  	// Output:
   381  	// SELECT * FROM "test" ORDER BY "a" ASC
   382  }
   383  
   384  func ExampleSelectDataset_Order_caseExpression() {
   385  	ds := goqu.From("test").Order(goqu.Case().When(goqu.C("num").Gt(10), 0).Else(1).Asc())
   386  	sql, _, _ := ds.ToSQL()
   387  	fmt.Println(sql)
   388  	// Output:
   389  	// SELECT * FROM "test" ORDER BY CASE  WHEN ("num" > 10) THEN 0 ELSE 1 END ASC
   390  }
   391  
   392  func ExampleSelectDataset_OrderAppend() {
   393  	ds := goqu.From("test").Order(goqu.C("a").Asc())
   394  	sql, _, _ := ds.OrderAppend(goqu.C("b").Desc().NullsLast()).ToSQL()
   395  	fmt.Println(sql)
   396  	// Output:
   397  	// SELECT * FROM "test" ORDER BY "a" ASC, "b" DESC NULLS LAST
   398  }
   399  
   400  func ExampleSelectDataset_OrderPrepend() {
   401  	ds := goqu.From("test").Order(goqu.C("a").Asc())
   402  	sql, _, _ := ds.OrderPrepend(goqu.C("b").Desc().NullsLast()).ToSQL()
   403  	fmt.Println(sql)
   404  	// Output:
   405  	// SELECT * FROM "test" ORDER BY "b" DESC NULLS LAST, "a" ASC
   406  }
   407  
   408  func ExampleSelectDataset_ClearOrder() {
   409  	ds := goqu.From("test").Order(goqu.C("a").Asc())
   410  	sql, _, _ := ds.ClearOrder().ToSQL()
   411  	fmt.Println(sql)
   412  	// Output:
   413  	// SELECT * FROM "test"
   414  }
   415  
   416  func ExampleSelectDataset_GroupBy() {
   417  	sql, _, _ := goqu.From("test").
   418  		Select(goqu.SUM("income").As("income_sum")).
   419  		GroupBy("age").
   420  		ToSQL()
   421  	fmt.Println(sql)
   422  	// Output:
   423  	// SELECT SUM("income") AS "income_sum" FROM "test" GROUP BY "age"
   424  }
   425  
   426  func ExampleSelectDataset_GroupByAppend() {
   427  	ds := goqu.From("test").
   428  		Select(goqu.SUM("income").As("income_sum")).
   429  		GroupBy("age")
   430  	sql, _, _ := ds.
   431  		GroupByAppend("job").
   432  		ToSQL()
   433  	fmt.Println(sql)
   434  	// the original dataset group by does not change
   435  	sql, _, _ = ds.ToSQL()
   436  	fmt.Println(sql)
   437  	// Output:
   438  	// SELECT SUM("income") AS "income_sum" FROM "test" GROUP BY "age", "job"
   439  	// SELECT SUM("income") AS "income_sum" FROM "test" GROUP BY "age"
   440  }
   441  
   442  func ExampleSelectDataset_Having() {
   443  	sql, _, _ := goqu.From("test").Having(goqu.SUM("income").Gt(1000)).ToSQL()
   444  	fmt.Println(sql)
   445  	sql, _, _ = goqu.From("test").GroupBy("age").Having(goqu.SUM("income").Gt(1000)).ToSQL()
   446  	fmt.Println(sql)
   447  	// Output:
   448  	// SELECT * FROM "test" HAVING (SUM("income") > 1000)
   449  	// SELECT * FROM "test" GROUP BY "age" HAVING (SUM("income") > 1000)
   450  }
   451  
   452  func ExampleSelectDataset_Window() {
   453  	ds := goqu.From("test").
   454  		Select(goqu.ROW_NUMBER().Over(goqu.W().PartitionBy("a").OrderBy(goqu.I("b").Asc())))
   455  	query, args, _ := ds.ToSQL()
   456  	fmt.Println(query, args)
   457  
   458  	ds = goqu.From("test").
   459  		Select(goqu.ROW_NUMBER().OverName(goqu.I("w"))).
   460  		Window(goqu.W("w").PartitionBy("a").OrderBy(goqu.I("b").Asc()))
   461  	query, args, _ = ds.ToSQL()
   462  	fmt.Println(query, args)
   463  
   464  	ds = goqu.From("test").
   465  		Select(goqu.ROW_NUMBER().OverName(goqu.I("w1"))).
   466  		Window(
   467  			goqu.W("w1").PartitionBy("a"),
   468  			goqu.W("w").Inherit("w1").OrderBy(goqu.I("b").Asc()),
   469  		)
   470  	query, args, _ = ds.ToSQL()
   471  	fmt.Println(query, args)
   472  
   473  	ds = goqu.From("test").
   474  		Select(goqu.ROW_NUMBER().Over(goqu.W().Inherit("w").OrderBy("b"))).
   475  		Window(goqu.W("w").PartitionBy("a"))
   476  	query, args, _ = ds.ToSQL()
   477  	fmt.Println(query, args)
   478  	// Output
   479  	// SELECT ROW_NUMBER() OVER (PARTITION BY "a" ORDER BY "b" ASC) FROM "test" []
   480  	// SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w" AS (PARTITION BY "a" ORDER BY "b" ASC) []
   481  	// SELECT ROW_NUMBER() OVER "w" FROM "test" WINDOW "w1" AS (PARTITION BY "a"), "w" AS ("w1" ORDER BY "b" ASC) []
   482  	// SELECT ROW_NUMBER() OVER ("w" ORDER BY "b") FROM "test" WINDOW "w" AS (PARTITION BY "a") []
   483  }
   484  
   485  func ExampleSelectDataset_Where() {
   486  	// By default everything is anded together
   487  	sql, _, _ := goqu.From("test").Where(goqu.Ex{
   488  		"a": goqu.Op{"gt": 10},
   489  		"b": goqu.Op{"lt": 10},
   490  		"c": nil,
   491  		"d": []string{"a", "b", "c"},
   492  	}).ToSQL()
   493  	fmt.Println(sql)
   494  	// You can use ExOr to get ORed expressions together
   495  	sql, _, _ = goqu.From("test").Where(goqu.ExOr{
   496  		"a": goqu.Op{"gt": 10},
   497  		"b": goqu.Op{"lt": 10},
   498  		"c": nil,
   499  		"d": []string{"a", "b", "c"},
   500  	}).ToSQL()
   501  	fmt.Println(sql)
   502  	// You can use Or with Ex to Or multiple Ex maps together
   503  	sql, _, _ = goqu.From("test").Where(
   504  		goqu.Or(
   505  			goqu.Ex{
   506  				"a": goqu.Op{"gt": 10},
   507  				"b": goqu.Op{"lt": 10},
   508  			},
   509  			goqu.Ex{
   510  				"c": nil,
   511  				"d": []string{"a", "b", "c"},
   512  			},
   513  		),
   514  	).ToSQL()
   515  	fmt.Println(sql)
   516  	// By default everything is anded together
   517  	sql, _, _ = goqu.From("test").Where(
   518  		goqu.C("a").Gt(10),
   519  		goqu.C("b").Lt(10),
   520  		goqu.C("c").IsNull(),
   521  		goqu.C("d").In("a", "b", "c"),
   522  	).ToSQL()
   523  	fmt.Println(sql)
   524  	// You can use a combination of Ors and Ands
   525  	sql, _, _ = goqu.From("test").Where(
   526  		goqu.Or(
   527  			goqu.C("a").Gt(10),
   528  			goqu.And(
   529  				goqu.C("b").Lt(10),
   530  				goqu.C("c").IsNull(),
   531  			),
   532  		),
   533  	).ToSQL()
   534  	fmt.Println(sql)
   535  	// Output:
   536  	// SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
   537  	// SELECT * FROM "test" WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c')))
   538  	// SELECT * FROM "test" WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))))
   539  	// SELECT * FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
   540  	// SELECT * FROM "test" WHERE (("a" > 10) OR (("b" < 10) AND ("c" IS NULL)))
   541  }
   542  
   543  func ExampleSelectDataset_Where_prepared() {
   544  	// By default everything is anded together
   545  	sql, args, _ := goqu.From("test").Prepared(true).Where(goqu.Ex{
   546  		"a": goqu.Op{"gt": 10},
   547  		"b": goqu.Op{"lt": 10},
   548  		"c": nil,
   549  		"d": []string{"a", "b", "c"},
   550  	}).ToSQL()
   551  	fmt.Println(sql, args)
   552  	// You can use ExOr to get ORed expressions together
   553  	sql, args, _ = goqu.From("test").Prepared(true).Where(goqu.ExOr{
   554  		"a": goqu.Op{"gt": 10},
   555  		"b": goqu.Op{"lt": 10},
   556  		"c": nil,
   557  		"d": []string{"a", "b", "c"},
   558  	}).ToSQL()
   559  	fmt.Println(sql, args)
   560  	// You can use Or with Ex to Or multiple Ex maps together
   561  	sql, args, _ = goqu.From("test").Prepared(true).Where(
   562  		goqu.Or(
   563  			goqu.Ex{
   564  				"a": goqu.Op{"gt": 10},
   565  				"b": goqu.Op{"lt": 10},
   566  			},
   567  			goqu.Ex{
   568  				"c": nil,
   569  				"d": []string{"a", "b", "c"},
   570  			},
   571  		),
   572  	).ToSQL()
   573  	fmt.Println(sql, args)
   574  	// By default everything is anded together
   575  	sql, args, _ = goqu.From("test").Prepared(true).Where(
   576  		goqu.C("a").Gt(10),
   577  		goqu.C("b").Lt(10),
   578  		goqu.C("c").IsNull(),
   579  		goqu.C("d").In("a", "b", "c"),
   580  	).ToSQL()
   581  	fmt.Println(sql, args)
   582  	// You can use a combination of Ors and Ands
   583  	sql, args, _ = goqu.From("test").Prepared(true).Where(
   584  		goqu.Or(
   585  			goqu.C("a").Gt(10),
   586  			goqu.And(
   587  				goqu.C("b").Lt(10),
   588  				goqu.C("c").IsNull(),
   589  			),
   590  		),
   591  	).ToSQL()
   592  	fmt.Println(sql, args)
   593  	// Output:
   594  	// SELECT * FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
   595  	// SELECT * FROM "test" WHERE (("a" > ?) OR ("b" < ?) OR ("c" IS NULL) OR ("d" IN (?, ?, ?))) [10 10 a b c]
   596  	// SELECT * FROM "test" WHERE ((("a" > ?) AND ("b" < ?)) OR (("c" IS NULL) AND ("d" IN (?, ?, ?)))) [10 10 a b c]
   597  	// SELECT * FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
   598  	// SELECT * FROM "test" WHERE (("a" > ?) OR (("b" < ?) AND ("c" IS NULL))) [10 10]
   599  }
   600  
   601  func ExampleSelectDataset_ClearWhere() {
   602  	ds := goqu.From("test").Where(
   603  		goqu.Or(
   604  			goqu.C("a").Gt(10),
   605  			goqu.And(
   606  				goqu.C("b").Lt(10),
   607  				goqu.C("c").IsNull(),
   608  			),
   609  		),
   610  	)
   611  	sql, _, _ := ds.ClearWhere().ToSQL()
   612  	fmt.Println(sql)
   613  	// Output:
   614  	// SELECT * FROM "test"
   615  }
   616  
   617  func ExampleSelectDataset_Join() {
   618  	sql, _, _ := goqu.From("test").Join(
   619  		goqu.T("test2"),
   620  		goqu.On(goqu.Ex{"test.fkey": goqu.I("test2.Id")}),
   621  	).ToSQL()
   622  	fmt.Println(sql)
   623  
   624  	sql, _, _ = goqu.From("test").Join(goqu.T("test2"), goqu.Using("common_column")).ToSQL()
   625  	fmt.Println(sql)
   626  
   627  	sql, _, _ = goqu.From("test").Join(
   628  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   629  		goqu.On(goqu.I("test.fkey").Eq(goqu.T("test2").Col("Id"))),
   630  	).ToSQL()
   631  	fmt.Println(sql)
   632  
   633  	sql, _, _ = goqu.From("test").Join(
   634  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   635  		goqu.On(goqu.T("test").Col("fkey").Eq(goqu.T("t").Col("Id"))),
   636  	).ToSQL()
   637  	fmt.Println(sql)
   638  	// Output:
   639  	// SELECT * FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
   640  	// SELECT * FROM "test" INNER JOIN "test2" USING ("common_column")
   641  	// SELECT * FROM "test" INNER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
   642  	// SELECT * FROM "test" INNER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")
   643  }
   644  
   645  func ExampleSelectDataset_InnerJoin() {
   646  	sql, _, _ := goqu.From("test").InnerJoin(
   647  		goqu.T("test2"),
   648  		goqu.On(goqu.Ex{
   649  			"test.fkey": goqu.I("test2.Id"),
   650  		}),
   651  	).ToSQL()
   652  	fmt.Println(sql)
   653  
   654  	sql, _, _ = goqu.From("test").InnerJoin(
   655  		goqu.T("test2"),
   656  		goqu.Using("common_column"),
   657  	).ToSQL()
   658  	fmt.Println(sql)
   659  
   660  	sql, _, _ = goqu.From("test").InnerJoin(
   661  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   662  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id"))),
   663  	).ToSQL()
   664  	fmt.Println(sql)
   665  
   666  	sql, _, _ = goqu.From("test").InnerJoin(
   667  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   668  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("t.Id"))),
   669  	).ToSQL()
   670  	fmt.Println(sql)
   671  	// Output:
   672  	// SELECT * FROM "test" INNER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
   673  	// SELECT * FROM "test" INNER JOIN "test2" USING ("common_column")
   674  	// SELECT * FROM "test" INNER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
   675  	// SELECT * FROM "test" INNER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")
   676  }
   677  
   678  func ExampleSelectDataset_FullOuterJoin() {
   679  	sql, _, _ := goqu.From("test").FullOuterJoin(
   680  		goqu.T("test2"),
   681  		goqu.On(goqu.Ex{
   682  			"test.fkey": goqu.I("test2.Id"),
   683  		}),
   684  	).ToSQL()
   685  	fmt.Println(sql)
   686  
   687  	sql, _, _ = goqu.From("test").FullOuterJoin(
   688  		goqu.T("test2"),
   689  		goqu.Using("common_column"),
   690  	).ToSQL()
   691  	fmt.Println(sql)
   692  
   693  	sql, _, _ = goqu.From("test").FullOuterJoin(
   694  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   695  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id"))),
   696  	).ToSQL()
   697  	fmt.Println(sql)
   698  
   699  	sql, _, _ = goqu.From("test").FullOuterJoin(
   700  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   701  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("t.Id"))),
   702  	).ToSQL()
   703  	fmt.Println(sql)
   704  	// Output:
   705  	// SELECT * FROM "test" FULL OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
   706  	// SELECT * FROM "test" FULL OUTER JOIN "test2" USING ("common_column")
   707  	// SELECT * FROM "test" FULL OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
   708  	// SELECT * FROM "test" FULL OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")
   709  }
   710  
   711  func ExampleSelectDataset_RightOuterJoin() {
   712  	sql, _, _ := goqu.From("test").RightOuterJoin(
   713  		goqu.T("test2"),
   714  		goqu.On(goqu.Ex{
   715  			"test.fkey": goqu.I("test2.Id"),
   716  		}),
   717  	).ToSQL()
   718  	fmt.Println(sql)
   719  
   720  	sql, _, _ = goqu.From("test").RightOuterJoin(
   721  		goqu.T("test2"),
   722  		goqu.Using("common_column"),
   723  	).ToSQL()
   724  	fmt.Println(sql)
   725  
   726  	sql, _, _ = goqu.From("test").RightOuterJoin(
   727  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   728  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id"))),
   729  	).ToSQL()
   730  	fmt.Println(sql)
   731  
   732  	sql, _, _ = goqu.From("test").RightOuterJoin(
   733  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   734  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("t.Id"))),
   735  	).ToSQL()
   736  	fmt.Println(sql)
   737  	// Output:
   738  	// SELECT * FROM "test" RIGHT OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
   739  	// SELECT * FROM "test" RIGHT OUTER JOIN "test2" USING ("common_column")
   740  	// SELECT * FROM "test" RIGHT OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
   741  	// SELECT * FROM "test" RIGHT OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")
   742  }
   743  
   744  func ExampleSelectDataset_LeftOuterJoin() {
   745  	sql, _, _ := goqu.From("test").LeftOuterJoin(
   746  		goqu.T("test2"),
   747  		goqu.On(goqu.Ex{
   748  			"test.fkey": goqu.I("test2.Id"),
   749  		}),
   750  	).ToSQL()
   751  	fmt.Println(sql)
   752  
   753  	sql, _, _ = goqu.From("test").LeftOuterJoin(
   754  		goqu.T("test2"),
   755  		goqu.Using("common_column"),
   756  	).ToSQL()
   757  	fmt.Println(sql)
   758  
   759  	sql, _, _ = goqu.From("test").LeftOuterJoin(
   760  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   761  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id"))),
   762  	).ToSQL()
   763  	fmt.Println(sql)
   764  
   765  	sql, _, _ = goqu.From("test").LeftOuterJoin(
   766  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   767  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("t.Id"))),
   768  	).ToSQL()
   769  	fmt.Println(sql)
   770  	// Output:
   771  	// SELECT * FROM "test" LEFT OUTER JOIN "test2" ON ("test"."fkey" = "test2"."Id")
   772  	// SELECT * FROM "test" LEFT OUTER JOIN "test2" USING ("common_column")
   773  	// SELECT * FROM "test" LEFT OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
   774  	// SELECT * FROM "test" LEFT OUTER JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")
   775  }
   776  
   777  func ExampleSelectDataset_FullJoin() {
   778  	sql, _, _ := goqu.From("test").FullJoin(
   779  		goqu.T("test2"),
   780  		goqu.On(goqu.Ex{
   781  			"test.fkey": goqu.I("test2.Id"),
   782  		}),
   783  	).ToSQL()
   784  	fmt.Println(sql)
   785  
   786  	sql, _, _ = goqu.From("test").FullJoin(
   787  		goqu.T("test2"),
   788  		goqu.Using("common_column"),
   789  	).ToSQL()
   790  	fmt.Println(sql)
   791  
   792  	sql, _, _ = goqu.From("test").FullJoin(
   793  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   794  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id"))),
   795  	).ToSQL()
   796  	fmt.Println(sql)
   797  
   798  	sql, _, _ = goqu.From("test").FullJoin(
   799  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   800  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("t.Id"))),
   801  	).ToSQL()
   802  	fmt.Println(sql)
   803  	// Output:
   804  	// SELECT * FROM "test" FULL JOIN "test2" ON ("test"."fkey" = "test2"."Id")
   805  	// SELECT * FROM "test" FULL JOIN "test2" USING ("common_column")
   806  	// SELECT * FROM "test" FULL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
   807  	// SELECT * FROM "test" FULL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")
   808  }
   809  
   810  func ExampleSelectDataset_RightJoin() {
   811  	sql, _, _ := goqu.From("test").RightJoin(
   812  		goqu.T("test2"),
   813  		goqu.On(goqu.Ex{
   814  			"test.fkey": goqu.I("test2.Id"),
   815  		}),
   816  	).ToSQL()
   817  	fmt.Println(sql)
   818  
   819  	sql, _, _ = goqu.From("test").RightJoin(
   820  		goqu.T("test2"),
   821  		goqu.Using("common_column"),
   822  	).ToSQL()
   823  	fmt.Println(sql)
   824  
   825  	sql, _, _ = goqu.From("test").RightJoin(
   826  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   827  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id"))),
   828  	).ToSQL()
   829  	fmt.Println(sql)
   830  
   831  	sql, _, _ = goqu.From("test").RightJoin(
   832  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   833  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("t.Id"))),
   834  	).ToSQL()
   835  	fmt.Println(sql)
   836  	// Output:
   837  	// SELECT * FROM "test" RIGHT JOIN "test2" ON ("test"."fkey" = "test2"."Id")
   838  	// SELECT * FROM "test" RIGHT JOIN "test2" USING ("common_column")
   839  	// SELECT * FROM "test" RIGHT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
   840  	// SELECT * FROM "test" RIGHT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")
   841  }
   842  
   843  func ExampleSelectDataset_LeftJoin() {
   844  	sql, _, _ := goqu.From("test").LeftJoin(
   845  		goqu.T("test2"),
   846  		goqu.On(goqu.Ex{
   847  			"test.fkey": goqu.I("test2.Id"),
   848  		}),
   849  	).ToSQL()
   850  	fmt.Println(sql)
   851  
   852  	sql, _, _ = goqu.From("test").LeftJoin(
   853  		goqu.T("test2"),
   854  		goqu.Using("common_column"),
   855  	).ToSQL()
   856  	fmt.Println(sql)
   857  
   858  	sql, _, _ = goqu.From("test").LeftJoin(
   859  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   860  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("test2.Id"))),
   861  	).ToSQL()
   862  	fmt.Println(sql)
   863  
   864  	sql, _, _ = goqu.From("test").LeftJoin(
   865  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   866  		goqu.On(goqu.I("test.fkey").Eq(goqu.I("t.Id"))),
   867  	).ToSQL()
   868  	fmt.Println(sql)
   869  	// Output:
   870  	// SELECT * FROM "test" LEFT JOIN "test2" ON ("test"."fkey" = "test2"."Id")
   871  	// SELECT * FROM "test" LEFT JOIN "test2" USING ("common_column")
   872  	// SELECT * FROM "test" LEFT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) ON ("test"."fkey" = "test2"."Id")
   873  	// SELECT * FROM "test" LEFT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t" ON ("test"."fkey" = "t"."Id")
   874  }
   875  
   876  func ExampleSelectDataset_NaturalJoin() {
   877  	sql, _, _ := goqu.From("test").NaturalJoin(goqu.T("test2")).ToSQL()
   878  	fmt.Println(sql)
   879  
   880  	sql, _, _ = goqu.From("test").NaturalJoin(
   881  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   882  	).ToSQL()
   883  	fmt.Println(sql)
   884  
   885  	sql, _, _ = goqu.From("test").NaturalJoin(
   886  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   887  	).ToSQL()
   888  	fmt.Println(sql)
   889  	// Output:
   890  	// SELECT * FROM "test" NATURAL JOIN "test2"
   891  	// SELECT * FROM "test" NATURAL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
   892  	// SELECT * FROM "test" NATURAL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"
   893  }
   894  
   895  func ExampleSelectDataset_NaturalLeftJoin() {
   896  	sql, _, _ := goqu.From("test").NaturalLeftJoin(goqu.T("test2")).ToSQL()
   897  	fmt.Println(sql)
   898  
   899  	sql, _, _ = goqu.From("test").NaturalLeftJoin(
   900  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   901  	).ToSQL()
   902  	fmt.Println(sql)
   903  
   904  	sql, _, _ = goqu.From("test").NaturalLeftJoin(
   905  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   906  	).ToSQL()
   907  	fmt.Println(sql)
   908  	// Output:
   909  	// SELECT * FROM "test" NATURAL LEFT JOIN "test2"
   910  	// SELECT * FROM "test" NATURAL LEFT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
   911  	// SELECT * FROM "test" NATURAL LEFT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"
   912  }
   913  
   914  func ExampleSelectDataset_NaturalRightJoin() {
   915  	sql, _, _ := goqu.From("test").NaturalRightJoin(goqu.T("test2")).ToSQL()
   916  	fmt.Println(sql)
   917  
   918  	sql, _, _ = goqu.From("test").NaturalRightJoin(
   919  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   920  	).ToSQL()
   921  	fmt.Println(sql)
   922  
   923  	sql, _, _ = goqu.From("test").NaturalRightJoin(
   924  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   925  	).ToSQL()
   926  	fmt.Println(sql)
   927  	// Output:
   928  	// SELECT * FROM "test" NATURAL RIGHT JOIN "test2"
   929  	// SELECT * FROM "test" NATURAL RIGHT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
   930  	// SELECT * FROM "test" NATURAL RIGHT JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"
   931  }
   932  
   933  func ExampleSelectDataset_NaturalFullJoin() {
   934  	sql, _, _ := goqu.From("test").NaturalFullJoin(goqu.T("test2")).ToSQL()
   935  	fmt.Println(sql)
   936  
   937  	sql, _, _ = goqu.From("test").NaturalFullJoin(
   938  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   939  	).ToSQL()
   940  	fmt.Println(sql)
   941  
   942  	sql, _, _ = goqu.From("test").NaturalFullJoin(
   943  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   944  	).ToSQL()
   945  	fmt.Println(sql)
   946  	// Output:
   947  	// SELECT * FROM "test" NATURAL FULL JOIN "test2"
   948  	// SELECT * FROM "test" NATURAL FULL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
   949  	// SELECT * FROM "test" NATURAL FULL JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"
   950  }
   951  
   952  func ExampleSelectDataset_CrossJoin() {
   953  	sql, _, _ := goqu.From("test").CrossJoin(goqu.T("test2")).ToSQL()
   954  	fmt.Println(sql)
   955  
   956  	sql, _, _ = goqu.From("test").CrossJoin(
   957  		goqu.From("test2").Where(goqu.C("amount").Gt(0)),
   958  	).ToSQL()
   959  	fmt.Println(sql)
   960  
   961  	sql, _, _ = goqu.From("test").CrossJoin(
   962  		goqu.From("test2").Where(goqu.C("amount").Gt(0)).As("t"),
   963  	).ToSQL()
   964  	fmt.Println(sql)
   965  	// Output:
   966  	// SELECT * FROM "test" CROSS JOIN "test2"
   967  	// SELECT * FROM "test" CROSS JOIN (SELECT * FROM "test2" WHERE ("amount" > 0))
   968  	// SELECT * FROM "test" CROSS JOIN (SELECT * FROM "test2" WHERE ("amount" > 0)) AS "t"
   969  }
   970  
   971  func ExampleSelectDataset_FromSelf() {
   972  	sql, _, _ := goqu.From("test").FromSelf().ToSQL()
   973  	fmt.Println(sql)
   974  	sql, _, _ = goqu.From("test").As("my_test_table").FromSelf().ToSQL()
   975  	fmt.Println(sql)
   976  	// Output:
   977  	// SELECT * FROM (SELECT * FROM "test") AS "t1"
   978  	// SELECT * FROM (SELECT * FROM "test") AS "my_test_table"
   979  }
   980  
   981  func ExampleSelectDataset_From() {
   982  	ds := goqu.From("test")
   983  	sql, _, _ := ds.From("test2").ToSQL()
   984  	fmt.Println(sql)
   985  	// Output:
   986  	// SELECT * FROM "test2"
   987  }
   988  
   989  func ExampleSelectDataset_From_withDataset() {
   990  	ds := goqu.From("test")
   991  	fromDs := ds.Where(goqu.C("age").Gt(10))
   992  	sql, _, _ := ds.From(fromDs).ToSQL()
   993  	fmt.Println(sql)
   994  	// Output:
   995  	// SELECT * FROM (SELECT * FROM "test" WHERE ("age" > 10)) AS "t1"
   996  }
   997  
   998  func ExampleSelectDataset_From_withAliasedDataset() {
   999  	ds := goqu.From("test")
  1000  	fromDs := ds.Where(goqu.C("age").Gt(10))
  1001  	sql, _, _ := ds.From(fromDs.As("test2")).ToSQL()
  1002  	fmt.Println(sql)
  1003  	// Output:
  1004  	// SELECT * FROM (SELECT * FROM "test" WHERE ("age" > 10)) AS "test2"
  1005  }
  1006  
  1007  func ExampleSelectDataset_Select() {
  1008  	sql, _, _ := goqu.From("test").Select("a", "b", "c").ToSQL()
  1009  	fmt.Println(sql)
  1010  	// Output:
  1011  	// SELECT "a", "b", "c" FROM "test"
  1012  }
  1013  
  1014  func ExampleSelectDataset_Select_withDataset() {
  1015  	ds := goqu.From("test")
  1016  	fromDs := ds.Select("age").Where(goqu.C("age").Gt(10))
  1017  	sql, _, _ := ds.From().Select(fromDs).ToSQL()
  1018  	fmt.Println(sql)
  1019  	// Output:
  1020  	// SELECT (SELECT "age" FROM "test" WHERE ("age" > 10))
  1021  }
  1022  
  1023  func ExampleSelectDataset_Select_withAliasedDataset() {
  1024  	ds := goqu.From("test")
  1025  	fromDs := ds.Select("age").Where(goqu.C("age").Gt(10))
  1026  	sql, _, _ := ds.From().Select(fromDs.As("ages")).ToSQL()
  1027  	fmt.Println(sql)
  1028  	// Output:
  1029  	// SELECT (SELECT "age" FROM "test" WHERE ("age" > 10)) AS "ages"
  1030  }
  1031  
  1032  func ExampleSelectDataset_Select_withLiteral() {
  1033  	sql, _, _ := goqu.From("test").Select(goqu.L("a + b").As("sum")).ToSQL()
  1034  	fmt.Println(sql)
  1035  	// Output:
  1036  	// SELECT a + b AS "sum" FROM "test"
  1037  }
  1038  
  1039  func ExampleSelectDataset_Select_withSQLFunctionExpression() {
  1040  	sql, _, _ := goqu.From("test").Select(
  1041  		goqu.COUNT("*").As("age_count"),
  1042  		goqu.MAX("age").As("max_age"),
  1043  		goqu.AVG("age").As("avg_age"),
  1044  	).ToSQL()
  1045  	fmt.Println(sql)
  1046  	// Output:
  1047  	// SELECT COUNT(*) AS "age_count", MAX("age") AS "max_age", AVG("age") AS "avg_age" FROM "test"
  1048  }
  1049  
  1050  func ExampleSelectDataset_Select_withStruct() {
  1051  	ds := goqu.From("test")
  1052  
  1053  	type myStruct struct {
  1054  		Name         string
  1055  		Address      string `db:"address"`
  1056  		EmailAddress string `db:"email_address"`
  1057  	}
  1058  
  1059  	// Pass with pointer
  1060  	sql, _, _ := ds.Select(&myStruct{}).ToSQL()
  1061  	fmt.Println(sql)
  1062  
  1063  	// Pass instance of
  1064  	sql, _, _ = ds.Select(myStruct{}).ToSQL()
  1065  	fmt.Println(sql)
  1066  
  1067  	type myStruct2 struct {
  1068  		myStruct
  1069  		Zipcode string `db:"zipcode"`
  1070  	}
  1071  
  1072  	// Pass pointer to struct with embedded struct
  1073  	sql, _, _ = ds.Select(&myStruct2{}).ToSQL()
  1074  	fmt.Println(sql)
  1075  
  1076  	// Pass instance of struct with embedded struct
  1077  	sql, _, _ = ds.Select(myStruct2{}).ToSQL()
  1078  	fmt.Println(sql)
  1079  
  1080  	var myStructs []myStruct
  1081  
  1082  	// Pass slice of structs, will only select columns from underlying type
  1083  	sql, _, _ = ds.Select(myStructs).ToSQL()
  1084  	fmt.Println(sql)
  1085  
  1086  	// Output:
  1087  	// SELECT "address", "email_address", "name" FROM "test"
  1088  	// SELECT "address", "email_address", "name" FROM "test"
  1089  	// SELECT "address", "email_address", "name", "zipcode" FROM "test"
  1090  	// SELECT "address", "email_address", "name", "zipcode" FROM "test"
  1091  	// SELECT "address", "email_address", "name" FROM "test"
  1092  }
  1093  
  1094  func ExampleSelectDataset_Distinct() {
  1095  	sql, _, _ := goqu.From("test").Select("a", "b").Distinct().ToSQL()
  1096  	fmt.Println(sql)
  1097  	// Output:
  1098  	// SELECT DISTINCT "a", "b" FROM "test"
  1099  }
  1100  
  1101  func ExampleSelectDataset_Distinct_on() {
  1102  	sql, _, _ := goqu.From("test").Distinct("a").ToSQL()
  1103  	fmt.Println(sql)
  1104  	// Output:
  1105  	// SELECT DISTINCT ON ("a") * FROM "test"
  1106  }
  1107  
  1108  func ExampleSelectDataset_Distinct_onWithLiteral() {
  1109  	sql, _, _ := goqu.From("test").Distinct(goqu.L("COALESCE(?, ?)", goqu.C("a"), "empty")).ToSQL()
  1110  	fmt.Println(sql)
  1111  	// Output:
  1112  	// SELECT DISTINCT ON (COALESCE("a", 'empty')) * FROM "test"
  1113  }
  1114  
  1115  func ExampleSelectDataset_Distinct_onCoalesce() {
  1116  	sql, _, _ := goqu.From("test").Distinct(goqu.COALESCE(goqu.C("a"), "empty")).ToSQL()
  1117  	fmt.Println(sql)
  1118  	// Output:
  1119  	// SELECT DISTINCT ON (COALESCE("a", 'empty')) * FROM "test"
  1120  }
  1121  
  1122  func ExampleSelectDataset_SelectAppend() {
  1123  	ds := goqu.From("test").Select("a", "b")
  1124  	sql, _, _ := ds.SelectAppend("c").ToSQL()
  1125  	fmt.Println(sql)
  1126  	ds = goqu.From("test").Select("a", "b").Distinct()
  1127  	sql, _, _ = ds.SelectAppend("c").ToSQL()
  1128  	fmt.Println(sql)
  1129  	// Output:
  1130  	// SELECT "a", "b", "c" FROM "test"
  1131  	// SELECT DISTINCT "a", "b", "c" FROM "test"
  1132  }
  1133  
  1134  func ExampleSelectDataset_ClearSelect() {
  1135  	ds := goqu.From("test").Select("a", "b")
  1136  	sql, _, _ := ds.ClearSelect().ToSQL()
  1137  	fmt.Println(sql)
  1138  	ds = goqu.From("test").Select("a", "b").Distinct()
  1139  	sql, _, _ = ds.ClearSelect().ToSQL()
  1140  	fmt.Println(sql)
  1141  	// Output:
  1142  	// SELECT * FROM "test"
  1143  	// SELECT * FROM "test"
  1144  }
  1145  
  1146  func ExampleSelectDataset_ToSQL() {
  1147  	sql, args, _ := goqu.From("items").Where(goqu.Ex{"a": 1}).ToSQL()
  1148  	fmt.Println(sql, args)
  1149  	// Output:
  1150  	// SELECT * FROM "items" WHERE ("a" = 1) []
  1151  }
  1152  
  1153  func ExampleSelectDataset_ToSQL_prepared() {
  1154  	sql, args, _ := goqu.From("items").Where(goqu.Ex{"a": 1}).Prepared(true).ToSQL()
  1155  	fmt.Println(sql, args)
  1156  	// Output:
  1157  	// SELECT * FROM "items" WHERE ("a" = ?) [1]
  1158  }
  1159  
  1160  func ExampleSelectDataset_Update() {
  1161  	type item struct {
  1162  		Address string `db:"address"`
  1163  		Name    string `db:"name"`
  1164  	}
  1165  	sql, args, _ := goqu.From("items").Update().Set(
  1166  		item{Name: "Test", Address: "111 Test Addr"},
  1167  	).ToSQL()
  1168  	fmt.Println(sql, args)
  1169  
  1170  	sql, args, _ = goqu.From("items").Update().Set(
  1171  		goqu.Record{"name": "Test", "address": "111 Test Addr"},
  1172  	).ToSQL()
  1173  	fmt.Println(sql, args)
  1174  
  1175  	sql, args, _ = goqu.From("items").Update().Set(
  1176  		map[string]interface{}{"name": "Test", "address": "111 Test Addr"},
  1177  	).ToSQL()
  1178  	fmt.Println(sql, args)
  1179  
  1180  	// Output:
  1181  	// UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
  1182  	// UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
  1183  	// UPDATE "items" SET "address"='111 Test Addr',"name"='Test' []
  1184  }
  1185  
  1186  func ExampleSelectDataset_Insert() {
  1187  	type item struct {
  1188  		ID      uint32 `db:"id" goqu:"skipinsert"`
  1189  		Address string `db:"address"`
  1190  		Name    string `db:"name"`
  1191  	}
  1192  	sql, args, _ := goqu.From("items").Insert().Rows(
  1193  		item{Name: "Test1", Address: "111 Test Addr"},
  1194  		item{Name: "Test2", Address: "112 Test Addr"},
  1195  	).ToSQL()
  1196  	fmt.Println(sql, args)
  1197  
  1198  	sql, args, _ = goqu.From("items").Insert().Rows(
  1199  		goqu.Record{"name": "Test1", "address": "111 Test Addr"},
  1200  		goqu.Record{"name": "Test2", "address": "112 Test Addr"},
  1201  	).ToSQL()
  1202  	fmt.Println(sql, args)
  1203  
  1204  	sql, args, _ = goqu.From("items").Insert().Rows(
  1205  		[]item{
  1206  			{Name: "Test1", Address: "111 Test Addr"},
  1207  			{Name: "Test2", Address: "112 Test Addr"},
  1208  		}).ToSQL()
  1209  	fmt.Println(sql, args)
  1210  
  1211  	sql, args, _ = goqu.From("items").Insert().Rows(
  1212  		[]goqu.Record{
  1213  			{"name": "Test1", "address": "111 Test Addr"},
  1214  			{"name": "Test2", "address": "112 Test Addr"},
  1215  		}).ToSQL()
  1216  	fmt.Println(sql, args)
  1217  	// Output:
  1218  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
  1219  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
  1220  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
  1221  	// INSERT INTO "items" ("address", "name") VALUES ('111 Test Addr', 'Test1'), ('112 Test Addr', 'Test2') []
  1222  }
  1223  
  1224  func ExampleSelectDataset_Delete() {
  1225  	sql, args, _ := goqu.From("items").Delete().ToSQL()
  1226  	fmt.Println(sql, args)
  1227  
  1228  	sql, args, _ = goqu.From("items").
  1229  		Where(goqu.Ex{"id": goqu.Op{"gt": 10}}).
  1230  		Delete().
  1231  		ToSQL()
  1232  	fmt.Println(sql, args)
  1233  
  1234  	// Output:
  1235  	// DELETE FROM "items" []
  1236  	// DELETE FROM "items" WHERE ("id" > 10) []
  1237  }
  1238  
  1239  func ExampleSelectDataset_Truncate() {
  1240  	sql, args, _ := goqu.From("items").Truncate().ToSQL()
  1241  	fmt.Println(sql, args)
  1242  	// Output:
  1243  	// TRUNCATE "items" []
  1244  }
  1245  
  1246  func ExampleSelectDataset_Prepared() {
  1247  	sql, args, _ := goqu.From("items").Prepared(true).Where(goqu.Ex{
  1248  		"col1": "a",
  1249  		"col2": 1,
  1250  		"col3": true,
  1251  		"col4": false,
  1252  		"col5": []string{"a", "b", "c"},
  1253  	}).ToSQL()
  1254  	fmt.Println(sql, args)
  1255  	// nolint:lll // sql statements are long
  1256  	// Output:
  1257  	// SELECT * FROM "items" WHERE (("col1" = ?) AND ("col2" = ?) AND ("col3" IS TRUE) AND ("col4" IS FALSE) AND ("col5" IN (?, ?, ?))) [a 1 a b c]
  1258  }
  1259  
  1260  func ExampleSelectDataset_ScanStructs() {
  1261  	type User struct {
  1262  		FirstName string `db:"first_name"`
  1263  		LastName  string `db:"last_name"`
  1264  	}
  1265  	db := getDB()
  1266  	var users []User
  1267  	if err := db.From("goqu_user").ScanStructs(&users); err != nil {
  1268  		fmt.Println(err.Error())
  1269  		return
  1270  	}
  1271  	fmt.Printf("\n%+v", users)
  1272  
  1273  	users = users[0:0]
  1274  	if err := db.From("goqu_user").Select("first_name").ScanStructs(&users); err != nil {
  1275  		fmt.Println(err.Error())
  1276  		return
  1277  	}
  1278  	fmt.Printf("\n%+v", users)
  1279  
  1280  	// Output:
  1281  	// [{FirstName:Bob LastName:Yukon} {FirstName:Sally LastName:Yukon} {FirstName:Vinita LastName:Yukon} {FirstName:John LastName:Doe}]
  1282  	// [{FirstName:Bob LastName:} {FirstName:Sally LastName:} {FirstName:Vinita LastName:} {FirstName:John LastName:}]
  1283  }
  1284  
  1285  func ExampleSelectDataset_ScanStructs_prepared() {
  1286  	type User struct {
  1287  		FirstName string `db:"first_name"`
  1288  		LastName  string `db:"last_name"`
  1289  	}
  1290  	db := getDB()
  1291  
  1292  	ds := db.From("goqu_user").
  1293  		Prepared(true).
  1294  		Where(goqu.Ex{
  1295  			"last_name": "Yukon",
  1296  		})
  1297  
  1298  	var users []User
  1299  	if err := ds.ScanStructs(&users); err != nil {
  1300  		fmt.Println(err.Error())
  1301  		return
  1302  	}
  1303  	fmt.Printf("\n%+v", users)
  1304  
  1305  	// Output:
  1306  	// [{FirstName:Bob LastName:Yukon} {FirstName:Sally LastName:Yukon} {FirstName:Vinita LastName:Yukon}]
  1307  }
  1308  
  1309  // In this example we create a new struct that has two structs that represent two table
  1310  // the User and Role fields are tagged with the table name
  1311  func ExampleSelectDataset_ScanStructs_withJoinAutoSelect() {
  1312  	type Role struct {
  1313  		UserID uint64 `db:"user_id"`
  1314  		Name   string `db:"name"`
  1315  	}
  1316  	type User struct {
  1317  		ID        uint64 `db:"id"`
  1318  		FirstName string `db:"first_name"`
  1319  		LastName  string `db:"last_name"`
  1320  	}
  1321  	type UserAndRole struct {
  1322  		User User `db:"goqu_user"` // tag as the "goqu_user" table
  1323  		Role Role `db:"user_role"` // tag as "user_role" table
  1324  	}
  1325  	db := getDB()
  1326  
  1327  	ds := db.
  1328  		From("goqu_user").
  1329  		Join(goqu.T("user_role"), goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))))
  1330  	var users []UserAndRole
  1331  	// Scan structs will auto build the
  1332  	if err := ds.ScanStructs(&users); err != nil {
  1333  		fmt.Println(err.Error())
  1334  		return
  1335  	}
  1336  	for _, u := range users {
  1337  		fmt.Printf("\n%+v", u)
  1338  	}
  1339  	// Output:
  1340  	// {User:{ID:1 FirstName:Bob LastName:Yukon} Role:{UserID:1 Name:Admin}}
  1341  	// {User:{ID:2 FirstName:Sally LastName:Yukon} Role:{UserID:2 Name:Manager}}
  1342  	// {User:{ID:3 FirstName:Vinita LastName:Yukon} Role:{UserID:3 Name:Manager}}
  1343  	// {User:{ID:4 FirstName:John LastName:Doe} Role:{UserID:4 Name:User}}
  1344  }
  1345  
  1346  // In this example we create a new struct that has the user properties as well as a nested
  1347  // Role struct from the join table
  1348  func ExampleSelectDataset_ScanStructs_withJoinManualSelect() {
  1349  	type Role struct {
  1350  		UserID uint64 `db:"user_id"`
  1351  		Name   string `db:"name"`
  1352  	}
  1353  	type User struct {
  1354  		ID        uint64 `db:"id"`
  1355  		FirstName string `db:"first_name"`
  1356  		LastName  string `db:"last_name"`
  1357  		Role      Role   `db:"user_role"` // tag as "user_role" table
  1358  	}
  1359  	db := getDB()
  1360  
  1361  	ds := db.
  1362  		Select(
  1363  			"goqu_user.id",
  1364  			"goqu_user.first_name",
  1365  			"goqu_user.last_name",
  1366  			// alias the fully qualified identifier `C` is important here so it doesnt parse it
  1367  			goqu.I("user_role.user_id").As(goqu.C("user_role.user_id")),
  1368  			goqu.I("user_role.name").As(goqu.C("user_role.name")),
  1369  		).
  1370  		From("goqu_user").
  1371  		Join(goqu.T("user_role"), goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))))
  1372  	var users []User
  1373  	if err := ds.ScanStructs(&users); err != nil {
  1374  		fmt.Println(err.Error())
  1375  		return
  1376  	}
  1377  	for _, u := range users {
  1378  		fmt.Printf("\n%+v", u)
  1379  	}
  1380  
  1381  	// Output:
  1382  	// {ID:1 FirstName:Bob LastName:Yukon Role:{UserID:1 Name:Admin}}
  1383  	// {ID:2 FirstName:Sally LastName:Yukon Role:{UserID:2 Name:Manager}}
  1384  	// {ID:3 FirstName:Vinita LastName:Yukon Role:{UserID:3 Name:Manager}}
  1385  	// {ID:4 FirstName:John LastName:Doe Role:{UserID:4 Name:User}}
  1386  }
  1387  
  1388  func ExampleSelectDataset_ScanStruct() {
  1389  	type User struct {
  1390  		FirstName string `db:"first_name"`
  1391  		LastName  string `db:"last_name"`
  1392  	}
  1393  	db := getDB()
  1394  	findUserByName := func(name string) {
  1395  		var user User
  1396  		ds := db.From("goqu_user").Where(goqu.C("first_name").Eq(name))
  1397  		found, err := ds.ScanStruct(&user)
  1398  		switch {
  1399  		case err != nil:
  1400  			fmt.Println(err.Error())
  1401  		case !found:
  1402  			fmt.Printf("No user found for first_name %s\n", name)
  1403  		default:
  1404  			fmt.Printf("Found user: %+v\n", user)
  1405  		}
  1406  	}
  1407  
  1408  	findUserByName("Bob")
  1409  	findUserByName("Zeb")
  1410  
  1411  	// Output:
  1412  	// Found user: {FirstName:Bob LastName:Yukon}
  1413  	// No user found for first_name Zeb
  1414  }
  1415  
  1416  // In this example we create a new struct that has two structs that represent two table
  1417  // the User and Role fields are tagged with the table name
  1418  func ExampleSelectDataset_ScanStruct_withJoinAutoSelect() {
  1419  	type Role struct {
  1420  		UserID uint64 `db:"user_id"`
  1421  		Name   string `db:"name"`
  1422  	}
  1423  	type User struct {
  1424  		ID        uint64 `db:"id"`
  1425  		FirstName string `db:"first_name"`
  1426  		LastName  string `db:"last_name"`
  1427  	}
  1428  	type UserAndRole struct {
  1429  		User User `db:"goqu_user"` // tag as the "goqu_user" table
  1430  		Role Role `db:"user_role"` // tag as "user_role" table
  1431  	}
  1432  	db := getDB()
  1433  	findUserAndRoleByName := func(name string) {
  1434  		var userAndRole UserAndRole
  1435  		ds := db.
  1436  			From("goqu_user").
  1437  			Join(
  1438  				goqu.T("user_role"),
  1439  				goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))),
  1440  			).
  1441  			Where(goqu.C("first_name").Eq(name))
  1442  		found, err := ds.ScanStruct(&userAndRole)
  1443  		switch {
  1444  		case err != nil:
  1445  			fmt.Println(err.Error())
  1446  		case !found:
  1447  			fmt.Printf("No user found for first_name %s\n", name)
  1448  		default:
  1449  			fmt.Printf("Found user and role: %+v\n", userAndRole)
  1450  		}
  1451  	}
  1452  
  1453  	findUserAndRoleByName("Bob")
  1454  	findUserAndRoleByName("Zeb")
  1455  	// Output:
  1456  	// Found user and role: {User:{ID:1 FirstName:Bob LastName:Yukon} Role:{UserID:1 Name:Admin}}
  1457  	// No user found for first_name Zeb
  1458  }
  1459  
  1460  // In this example we create a new struct that has the user properties as well as a nested
  1461  // Role struct from the join table
  1462  func ExampleSelectDataset_ScanStruct_withJoinManualSelect() {
  1463  	type Role struct {
  1464  		UserID uint64 `db:"user_id"`
  1465  		Name   string `db:"name"`
  1466  	}
  1467  	type User struct {
  1468  		ID        uint64 `db:"id"`
  1469  		FirstName string `db:"first_name"`
  1470  		LastName  string `db:"last_name"`
  1471  		Role      Role   `db:"user_role"` // tag as "user_role" table
  1472  	}
  1473  	db := getDB()
  1474  	findUserByName := func(name string) {
  1475  		var userAndRole User
  1476  		ds := db.
  1477  			Select(
  1478  				"goqu_user.id",
  1479  				"goqu_user.first_name",
  1480  				"goqu_user.last_name",
  1481  				// alias the fully qualified identifier `C` is important here so it doesnt parse it
  1482  				goqu.I("user_role.user_id").As(goqu.C("user_role.user_id")),
  1483  				goqu.I("user_role.name").As(goqu.C("user_role.name")),
  1484  			).
  1485  			From("goqu_user").
  1486  			Join(
  1487  				goqu.T("user_role"),
  1488  				goqu.On(goqu.I("goqu_user.id").Eq(goqu.I("user_role.user_id"))),
  1489  			).
  1490  			Where(goqu.C("first_name").Eq(name))
  1491  		found, err := ds.ScanStruct(&userAndRole)
  1492  		switch {
  1493  		case err != nil:
  1494  			fmt.Println(err.Error())
  1495  		case !found:
  1496  			fmt.Printf("No user found for first_name %s\n", name)
  1497  		default:
  1498  			fmt.Printf("Found user and role: %+v\n", userAndRole)
  1499  		}
  1500  	}
  1501  
  1502  	findUserByName("Bob")
  1503  	findUserByName("Zeb")
  1504  
  1505  	// Output:
  1506  	// Found user and role: {ID:1 FirstName:Bob LastName:Yukon Role:{UserID:1 Name:Admin}}
  1507  	// No user found for first_name Zeb
  1508  }
  1509  
  1510  func ExampleSelectDataset_ScanVals() {
  1511  	var ids []int64
  1512  	if err := getDB().From("goqu_user").Select("id").ScanVals(&ids); err != nil {
  1513  		fmt.Println(err.Error())
  1514  		return
  1515  	}
  1516  	fmt.Printf("UserIds = %+v", ids)
  1517  
  1518  	// Output:
  1519  	// UserIds = [1 2 3 4]
  1520  }
  1521  
  1522  func ExampleSelectDataset_ScanVal() {
  1523  	db := getDB()
  1524  	findUserIDByName := func(name string) {
  1525  		var id int64
  1526  		ds := db.From("goqu_user").
  1527  			Select("id").
  1528  			Where(goqu.C("first_name").Eq(name))
  1529  
  1530  		found, err := ds.ScanVal(&id)
  1531  		switch {
  1532  		case err != nil:
  1533  			fmt.Println(err.Error())
  1534  		case !found:
  1535  			fmt.Printf("No id found for user %s", name)
  1536  		default:
  1537  			fmt.Printf("\nFound userId: %+v\n", id)
  1538  		}
  1539  	}
  1540  
  1541  	findUserIDByName("Bob")
  1542  	findUserIDByName("Zeb")
  1543  	// Output:
  1544  	// Found userId: 1
  1545  	// No id found for user Zeb
  1546  }
  1547  
  1548  func ExampleSelectDataset_Count() {
  1549  	count, err := getDB().From("goqu_user").Count()
  1550  	if err != nil {
  1551  		fmt.Println(err.Error())
  1552  		return
  1553  	}
  1554  	fmt.Printf("Count is %d", count)
  1555  
  1556  	// Output:
  1557  	// Count is 4
  1558  }
  1559  
  1560  func ExampleSelectDataset_Pluck() {
  1561  	var lastNames []string
  1562  	if err := getDB().From("goqu_user").Pluck(&lastNames, "last_name"); err != nil {
  1563  		fmt.Println(err.Error())
  1564  		return
  1565  	}
  1566  	fmt.Printf("LastNames = %+v", lastNames)
  1567  
  1568  	// Output:
  1569  	// LastNames = [Yukon Yukon Yukon Doe]
  1570  }
  1571  
  1572  func ExampleSelectDataset_Executor_scannerScanStruct() {
  1573  	type User struct {
  1574  		FirstName string `db:"first_name"`
  1575  		LastName  string `db:"last_name"`
  1576  	}
  1577  	db := getDB()
  1578  
  1579  	scanner, err := db.
  1580  		From("goqu_user").
  1581  		Select("first_name", "last_name").
  1582  		Where(goqu.Ex{
  1583  			"last_name": "Yukon",
  1584  		}).
  1585  		Executor().
  1586  		Scanner()
  1587  	if err != nil {
  1588  		fmt.Println(err.Error())
  1589  		return
  1590  	}
  1591  
  1592  	defer scanner.Close()
  1593  
  1594  	for scanner.Next() {
  1595  		u := User{}
  1596  
  1597  		err = scanner.ScanStruct(&u)
  1598  		if err != nil {
  1599  			fmt.Println(err.Error())
  1600  			return
  1601  		}
  1602  
  1603  		fmt.Printf("\n%+v", u)
  1604  	}
  1605  
  1606  	if scanner.Err() != nil {
  1607  		fmt.Println(scanner.Err().Error())
  1608  	}
  1609  
  1610  	// Output:
  1611  	// {FirstName:Bob LastName:Yukon}
  1612  	// {FirstName:Sally LastName:Yukon}
  1613  	// {FirstName:Vinita LastName:Yukon}
  1614  }
  1615  
  1616  func ExampleSelectDataset_Executor_scannerScanVal() {
  1617  	db := getDB()
  1618  
  1619  	scanner, err := db.
  1620  		From("goqu_user").
  1621  		Select("first_name").
  1622  		Where(goqu.Ex{
  1623  			"last_name": "Yukon",
  1624  		}).
  1625  		Executor().
  1626  		Scanner()
  1627  	if err != nil {
  1628  		fmt.Println(err.Error())
  1629  		return
  1630  	}
  1631  
  1632  	defer scanner.Close()
  1633  
  1634  	for scanner.Next() {
  1635  		name := ""
  1636  
  1637  		err = scanner.ScanVal(&name)
  1638  		if err != nil {
  1639  			fmt.Println(err.Error())
  1640  			return
  1641  		}
  1642  
  1643  		fmt.Println(name)
  1644  	}
  1645  
  1646  	if scanner.Err() != nil {
  1647  		fmt.Println(scanner.Err().Error())
  1648  	}
  1649  
  1650  	// Output:
  1651  	// Bob
  1652  	// Sally
  1653  	// Vinita
  1654  }
  1655  
  1656  func ExampleForUpdate() {
  1657  	sql, args, _ := goqu.From("test").ForUpdate(exp.Wait).ToSQL()
  1658  	fmt.Println(sql, args)
  1659  
  1660  	// Output:
  1661  	// SELECT * FROM "test" FOR UPDATE  []
  1662  }
  1663  
  1664  func ExampleForUpdate_of() {
  1665  	sql, args, _ := goqu.From("test").ForUpdate(exp.Wait, goqu.T("test")).ToSQL()
  1666  	fmt.Println(sql, args)
  1667  
  1668  	// Output:
  1669  	// SELECT * FROM "test" FOR UPDATE OF "test"  []
  1670  }
  1671  
  1672  func ExampleForUpdate_ofMultiple() {
  1673  	sql, args, _ := goqu.From("table1").Join(
  1674  		goqu.T("table2"),
  1675  		goqu.On(goqu.I("table2.id").Eq(goqu.I("table1.id"))),
  1676  	).ForUpdate(
  1677  		exp.Wait,
  1678  		goqu.T("table1"),
  1679  		goqu.T("table2"),
  1680  	).ToSQL()
  1681  	fmt.Println(sql, args)
  1682  
  1683  	// Output:
  1684  	// SELECT * FROM "table1" INNER JOIN "table2" ON ("table2"."id" = "table1"."id") FOR UPDATE OF "table1", "table2"  []
  1685  }
  1686  

View as plain text