delete_dataset_example_test.go

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

     1  package goqu_test
     2  
     3  import (
     4  	"fmt"
     5  
     6  	"github.com/doug-martin/goqu/v9"
     7  	_ "github.com/doug-martin/goqu/v9/dialect/mysql"
     8  )
     9  
    10  func ExampleDelete() {
    11  	ds := goqu.Delete("items")
    12  
    13  	sql, args, _ := ds.ToSQL()
    14  	fmt.Println(sql, args)
    15  
    16  	// Output:
    17  	// DELETE FROM "items" []
    18  }
    19  
    20  func ExampleDeleteDataset_Executor() {
    21  	db := getDB()
    22  
    23  	de := db.Delete("goqu_user").
    24  		Where(goqu.Ex{"first_name": "Bob"}).
    25  		Executor()
    26  	if r, err := de.Exec(); err != nil {
    27  		fmt.Println(err.Error())
    28  	} else {
    29  		c, _ := r.RowsAffected()
    30  		fmt.Printf("Deleted %d users", c)
    31  	}
    32  
    33  	// Output:
    34  	// Deleted 1 users
    35  }
    36  
    37  func ExampleDeleteDataset_Executor_returning() {
    38  	db := getDB()
    39  
    40  	de := db.Delete("goqu_user").
    41  		Where(goqu.C("last_name").Eq("Yukon")).
    42  		Returning(goqu.C("id")).
    43  		Executor()
    44  
    45  	var ids []int64
    46  	if err := de.ScanVals(&ids); err != nil {
    47  		fmt.Println(err.Error())
    48  	} else {
    49  		fmt.Printf("Deleted users [ids:=%+v]", ids)
    50  	}
    51  
    52  	// Output:
    53  	// Deleted users [ids:=[1 2 3]]
    54  }
    55  
    56  func ExampleDeleteDataset_With() {
    57  	sql, _, _ := goqu.Delete("test").
    58  		With("check_vals(val)", goqu.From().Select(goqu.L("123"))).
    59  		Where(goqu.C("val").Eq(goqu.From("check_vals").Select("val"))).
    60  		ToSQL()
    61  	fmt.Println(sql)
    62  
    63  	// Output:
    64  	// WITH check_vals(val) AS (SELECT 123) DELETE FROM "test" WHERE ("val" IN (SELECT "val" FROM "check_vals"))
    65  }
    66  
    67  func ExampleDeleteDataset_WithRecursive() {
    68  	sql, _, _ := goqu.Delete("nums").
    69  		WithRecursive("nums(x)",
    70  			goqu.From().Select(goqu.L("1")).
    71  				UnionAll(goqu.From("nums").
    72  					Select(goqu.L("x+1")).Where(goqu.C("x").Lt(5)))).
    73  		ToSQL()
    74  	fmt.Println(sql)
    75  	// Output:
    76  	// WITH RECURSIVE nums(x) AS (SELECT 1 UNION ALL (SELECT x+1 FROM "nums" WHERE ("x" < 5))) DELETE FROM "nums"
    77  }
    78  
    79  func ExampleDeleteDataset_Where() {
    80  	// By default everything is anded together
    81  	sql, _, _ := goqu.Delete("test").Where(goqu.Ex{
    82  		"a": goqu.Op{"gt": 10},
    83  		"b": goqu.Op{"lt": 10},
    84  		"c": nil,
    85  		"d": []string{"a", "b", "c"},
    86  	}).ToSQL()
    87  	fmt.Println(sql)
    88  	// You can use ExOr to get ORed expressions together
    89  	sql, _, _ = goqu.Delete("test").Where(goqu.ExOr{
    90  		"a": goqu.Op{"gt": 10},
    91  		"b": goqu.Op{"lt": 10},
    92  		"c": nil,
    93  		"d": []string{"a", "b", "c"},
    94  	}).ToSQL()
    95  	fmt.Println(sql)
    96  	// You can use Or with Ex to Or multiple Ex maps together
    97  	sql, _, _ = goqu.Delete("test").Where(
    98  		goqu.Or(
    99  			goqu.Ex{
   100  				"a": goqu.Op{"gt": 10},
   101  				"b": goqu.Op{"lt": 10},
   102  			},
   103  			goqu.Ex{
   104  				"c": nil,
   105  				"d": []string{"a", "b", "c"},
   106  			},
   107  		),
   108  	).ToSQL()
   109  	fmt.Println(sql)
   110  	// By default everything is anded together
   111  	sql, _, _ = goqu.Delete("test").Where(
   112  		goqu.C("a").Gt(10),
   113  		goqu.C("b").Lt(10),
   114  		goqu.C("c").IsNull(),
   115  		goqu.C("d").In("a", "b", "c"),
   116  	).ToSQL()
   117  	fmt.Println(sql)
   118  	// You can use a combination of Ors and Ands
   119  	sql, _, _ = goqu.Delete("test").Where(
   120  		goqu.Or(
   121  			goqu.C("a").Gt(10),
   122  			goqu.And(
   123  				goqu.C("b").Lt(10),
   124  				goqu.C("c").IsNull(),
   125  			),
   126  		),
   127  	).ToSQL()
   128  	fmt.Println(sql)
   129  	// Output:
   130  	// DELETE FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
   131  	// DELETE FROM "test" WHERE (("a" > 10) OR ("b" < 10) OR ("c" IS NULL) OR ("d" IN ('a', 'b', 'c')))
   132  	// DELETE FROM "test" WHERE ((("a" > 10) AND ("b" < 10)) OR (("c" IS NULL) AND ("d" IN ('a', 'b', 'c'))))
   133  	// DELETE FROM "test" WHERE (("a" > 10) AND ("b" < 10) AND ("c" IS NULL) AND ("d" IN ('a', 'b', 'c')))
   134  	// DELETE FROM "test" WHERE (("a" > 10) OR (("b" < 10) AND ("c" IS NULL)))
   135  }
   136  
   137  func ExampleDeleteDataset_Where_prepared() {
   138  	// By default everything is anded together
   139  	sql, args, _ := goqu.Delete("test").Prepared(true).Where(goqu.Ex{
   140  		"a": goqu.Op{"gt": 10},
   141  		"b": goqu.Op{"lt": 10},
   142  		"c": nil,
   143  		"d": []string{"a", "b", "c"},
   144  	}).ToSQL()
   145  	fmt.Println(sql, args)
   146  	// You can use ExOr to get ORed expressions together
   147  	sql, args, _ = goqu.Delete("test").Prepared(true).Where(goqu.ExOr{
   148  		"a": goqu.Op{"gt": 10},
   149  		"b": goqu.Op{"lt": 10},
   150  		"c": nil,
   151  		"d": []string{"a", "b", "c"},
   152  	}).ToSQL()
   153  	fmt.Println(sql, args)
   154  	// You can use Or with Ex to Or multiple Ex maps together
   155  	sql, args, _ = goqu.Delete("test").Prepared(true).Where(
   156  		goqu.Or(
   157  			goqu.Ex{
   158  				"a": goqu.Op{"gt": 10},
   159  				"b": goqu.Op{"lt": 10},
   160  			},
   161  			goqu.Ex{
   162  				"c": nil,
   163  				"d": []string{"a", "b", "c"},
   164  			},
   165  		),
   166  	).ToSQL()
   167  	fmt.Println(sql, args)
   168  	// By default everything is anded together
   169  	sql, args, _ = goqu.Delete("test").Prepared(true).Where(
   170  		goqu.C("a").Gt(10),
   171  		goqu.C("b").Lt(10),
   172  		goqu.C("c").IsNull(),
   173  		goqu.C("d").In("a", "b", "c"),
   174  	).ToSQL()
   175  	fmt.Println(sql, args)
   176  	// You can use a combination of Ors and Ands
   177  	sql, args, _ = goqu.Delete("test").Prepared(true).Where(
   178  		goqu.Or(
   179  			goqu.C("a").Gt(10),
   180  			goqu.And(
   181  				goqu.C("b").Lt(10),
   182  				goqu.C("c").IsNull(),
   183  			),
   184  		),
   185  	).ToSQL()
   186  	fmt.Println(sql, args)
   187  	// Output:
   188  	// DELETE FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
   189  	// DELETE FROM "test" WHERE (("a" > ?) OR ("b" < ?) OR ("c" IS NULL) OR ("d" IN (?, ?, ?))) [10 10 a b c]
   190  	// DELETE FROM "test" WHERE ((("a" > ?) AND ("b" < ?)) OR (("c" IS NULL) AND ("d" IN (?, ?, ?)))) [10 10 a b c]
   191  	// DELETE FROM "test" WHERE (("a" > ?) AND ("b" < ?) AND ("c" IS NULL) AND ("d" IN (?, ?, ?))) [10 10 a b c]
   192  	// DELETE FROM "test" WHERE (("a" > ?) OR (("b" < ?) AND ("c" IS NULL))) [10 10]
   193  }
   194  
   195  func ExampleDeleteDataset_ClearWhere() {
   196  	ds := goqu.Delete("test").Where(
   197  		goqu.Or(
   198  			goqu.C("a").Gt(10),
   199  			goqu.And(
   200  				goqu.C("b").Lt(10),
   201  				goqu.C("c").IsNull(),
   202  			),
   203  		),
   204  	)
   205  	sql, _, _ := ds.ClearWhere().ToSQL()
   206  	fmt.Println(sql)
   207  	// Output:
   208  	// DELETE FROM "test"
   209  }
   210  
   211  func ExampleDeleteDataset_Limit() {
   212  	ds := goqu.Dialect("mysql").Delete("test").Limit(10)
   213  	sql, _, _ := ds.ToSQL()
   214  	fmt.Println(sql)
   215  	// Output:
   216  	// DELETE FROM `test` LIMIT 10
   217  }
   218  
   219  func ExampleDeleteDataset_LimitAll() {
   220  	// Using mysql dialect because it supports limit on delete
   221  	ds := goqu.Dialect("mysql").Delete("test").LimitAll()
   222  	sql, _, _ := ds.ToSQL()
   223  	fmt.Println(sql)
   224  	// Output:
   225  	// DELETE FROM `test` LIMIT ALL
   226  }
   227  
   228  func ExampleDeleteDataset_ClearLimit() {
   229  	// Using mysql dialect because it supports limit on delete
   230  	ds := goqu.Dialect("mysql").Delete("test").Limit(10)
   231  	sql, _, _ := ds.ClearLimit().ToSQL()
   232  	fmt.Println(sql)
   233  	// Output:
   234  	// DELETE `test` FROM `test`
   235  }
   236  
   237  func ExampleDeleteDataset_Order() {
   238  	// use mysql dialect because it supports order by on deletes
   239  	ds := goqu.Dialect("mysql").Delete("test").Order(goqu.C("a").Asc())
   240  	sql, _, _ := ds.ToSQL()
   241  	fmt.Println(sql)
   242  	// Output:
   243  	// DELETE FROM `test` ORDER BY `a` ASC
   244  }
   245  
   246  func ExampleDeleteDataset_OrderAppend() {
   247  	// use mysql dialect because it supports order by on deletes
   248  	ds := goqu.Dialect("mysql").Delete("test").Order(goqu.C("a").Asc())
   249  	sql, _, _ := ds.OrderAppend(goqu.C("b").Desc().NullsLast()).ToSQL()
   250  	fmt.Println(sql)
   251  	// Output:
   252  	// DELETE FROM `test` ORDER BY `a` ASC, `b` DESC NULLS LAST
   253  }
   254  
   255  func ExampleDeleteDataset_OrderPrepend() {
   256  	// use mysql dialect because it supports order by on deletes
   257  	ds := goqu.Dialect("mysql").Delete("test").Order(goqu.C("a").Asc())
   258  	sql, _, _ := ds.OrderPrepend(goqu.C("b").Desc().NullsLast()).ToSQL()
   259  	fmt.Println(sql)
   260  	// Output:
   261  	// DELETE FROM `test` ORDER BY `b` DESC NULLS LAST, `a` ASC
   262  }
   263  
   264  func ExampleDeleteDataset_ClearOrder() {
   265  	ds := goqu.Delete("test").Order(goqu.C("a").Asc())
   266  	sql, _, _ := ds.ClearOrder().ToSQL()
   267  	fmt.Println(sql)
   268  	// Output:
   269  	// DELETE FROM "test"
   270  }
   271  
   272  func ExampleDeleteDataset_ToSQL() {
   273  	sql, args, _ := goqu.Delete("items").ToSQL()
   274  	fmt.Println(sql, args)
   275  
   276  	sql, args, _ = goqu.Delete("items").
   277  		Where(goqu.Ex{"id": goqu.Op{"gt": 10}}).
   278  		ToSQL()
   279  	fmt.Println(sql, args)
   280  
   281  	// Output:
   282  	// DELETE FROM "items" []
   283  	// DELETE FROM "items" WHERE ("id" > 10) []
   284  }
   285  
   286  func ExampleDeleteDataset_Prepared() {
   287  	sql, args, _ := goqu.Delete("items").Prepared(true).ToSQL()
   288  	fmt.Println(sql, args)
   289  
   290  	sql, args, _ = goqu.Delete("items").
   291  		Prepared(true).
   292  		Where(goqu.Ex{"id": goqu.Op{"gt": 10}}).
   293  		ToSQL()
   294  	fmt.Println(sql, args)
   295  
   296  	// Output:
   297  	// DELETE FROM "items" []
   298  	// DELETE FROM "items" WHERE ("id" > ?) [10]
   299  }
   300  
   301  func ExampleDeleteDataset_Returning() {
   302  	ds := goqu.Delete("items")
   303  	sql, args, _ := ds.Returning("id").ToSQL()
   304  	fmt.Println(sql, args)
   305  
   306  	sql, args, _ = ds.Returning("id").Where(goqu.C("id").IsNotNull()).ToSQL()
   307  	fmt.Println(sql, args)
   308  
   309  	// Output:
   310  	// DELETE FROM "items" RETURNING "id" []
   311  	// DELETE FROM "items" WHERE ("id" IS NOT NULL) RETURNING "id" []
   312  }
   313
View as plain text