github.com/yongjacky/phoenix-go-orm-builder@v0.3.5/README.md (about) 1 # SQL builder 2 3 [](https://drone.gitea.com/xorm/builder) [](http://gocover.io/xorm.io/builder) 4 [](https://goreportcard.com/report/xorm.io/builder) 5 6 Package builder is a lightweight and fast SQL builder for Go and XORM. 7 8 Make sure you have installed Go 1.8+ and then: 9 10 go get xorm.io/builder 11 12 # Insert 13 14 ```Go 15 sql, args, err := builder.Insert(Eq{"c": 1, "d": 2}).Into("table1").ToSQL() 16 17 // INSERT INTO table1 SELECT * FROM table2 18 sql, err := builder.Insert().Into("table1").Select().From("table2").ToBoundSQL() 19 20 // INSERT INTO table1 (a, b) SELECT b, c FROM table2 21 sql, err = builder.Insert("a, b").Into("table1").Select("b, c").From("table2").ToBoundSQL() 22 ``` 23 24 # Select 25 26 ```Go 27 // Simple Query 28 sql, args, err := Select("c, d").From("table1").Where(Eq{"a": 1}).ToSQL() 29 // With join 30 sql, args, err = Select("c, d").From("table1").LeftJoin("table2", Eq{"table1.id": 1}.And(Lt{"table2.id": 3})). 31 RightJoin("table3", "table2.id = table3.tid").Where(Eq{"a": 1}).ToSQL() 32 // From sub query 33 sql, args, err := Select("sub.id").From(Select("c").From("table1").Where(Eq{"a": 1}), "sub").Where(Eq{"b": 1}).ToSQL() 34 // From union query 35 sql, args, err = Select("sub.id").From( 36 Select("id").From("table1").Where(Eq{"a": 1}).Union("all", Select("id").From("table1").Where(Eq{"a": 2})),"sub"). 37 Where(Eq{"b": 1}).ToSQL() 38 // With order by 39 sql, args, err = Select("a", "b", "c").From("table1").Where(Eq{"f1": "v1", "f2": "v2"}). 40 OrderBy("a ASC").ToSQL() 41 // With limit. 42 // Be careful! You should set up specific dialect for builder before performing a query with LIMIT 43 sql, args, err = Dialect(MYSQL).Select("a", "b", "c").From("table1").OrderBy("a ASC"). 44 Limit(5, 10).ToSQL() 45 ``` 46 47 # Update 48 49 ```Go 50 sql, args, err := Update(Eq{"a": 2}).From("table1").Where(Eq{"a": 1}).ToSQL() 51 ``` 52 53 # Delete 54 55 ```Go 56 sql, args, err := Delete(Eq{"a": 1}).From("table1").ToSQL() 57 ``` 58 59 # Union 60 61 ```Go 62 sql, args, err := Select("*").From("a").Where(Eq{"status": "1"}). 63 Union("all", Select("*").From("a").Where(Eq{"status": "2"})). 64 Union("distinct", Select("*").From("a").Where(Eq{"status": "3"})). 65 Union("", Select("*").From("a").Where(Eq{"status": "4"})). 66 ToSQL() 67 ``` 68 69 # Conditions 70 71 * `Eq` is a redefine of a map, you can give one or more conditions to `Eq` 72 73 ```Go 74 import . "xorm.io/builder" 75 76 sql, args, _ := ToSQL(Eq{"a":1}) 77 // a=? [1] 78 sql, args, _ := ToSQL(Eq{"b":"c"}.And(Eq{"c": 0})) 79 // b=? AND c=? ["c", 0] 80 sql, args, _ := ToSQL(Eq{"b":"c", "c":0}) 81 // b=? AND c=? ["c", 0] 82 sql, args, _ := ToSQL(Eq{"b":"c"}.Or(Eq{"b":"d"})) 83 // b=? OR b=? ["c", "d"] 84 sql, args, _ := ToSQL(Eq{"b": []string{"c", "d"}}) 85 // b IN (?,?) ["c", "d"] 86 sql, args, _ := ToSQL(Eq{"b": 1, "c":[]int{2, 3}}) 87 // b=? AND c IN (?,?) [1, 2, 3] 88 ``` 89 90 * `Neq` is the same to `Eq` 91 92 ```Go 93 import . "xorm.io/builder" 94 95 sql, args, _ := ToSQL(Neq{"a":1}) 96 // a<>? [1] 97 sql, args, _ := ToSQL(Neq{"b":"c"}.And(Neq{"c": 0})) 98 // b<>? AND c<>? ["c", 0] 99 sql, args, _ := ToSQL(Neq{"b":"c", "c":0}) 100 // b<>? AND c<>? ["c", 0] 101 sql, args, _ := ToSQL(Neq{"b":"c"}.Or(Neq{"b":"d"})) 102 // b<>? OR b<>? ["c", "d"] 103 sql, args, _ := ToSQL(Neq{"b": []string{"c", "d"}}) 104 // b NOT IN (?,?) ["c", "d"] 105 sql, args, _ := ToSQL(Neq{"b": 1, "c":[]int{2, 3}}) 106 // b<>? AND c NOT IN (?,?) [1, 2, 3] 107 ``` 108 109 * `Gt`, `Gte`, `Lt`, `Lte` 110 111 ```Go 112 import . "xorm.io/builder" 113 114 sql, args, _ := ToSQL(Gt{"a", 1}.And(Gte{"b", 2})) 115 // a>? AND b>=? [1, 2] 116 sql, args, _ := ToSQL(Lt{"a", 1}.Or(Lte{"b", 2})) 117 // a<? OR b<=? [1, 2] 118 ``` 119 120 * `Like` 121 122 ```Go 123 import . "xorm.io/builder" 124 125 sql, args, _ := ToSQL(Like{"a", "c"}) 126 // a LIKE ? [%c%] 127 ``` 128 129 * `Expr` you can customerize your sql with `Expr` 130 131 ```Go 132 import . "xorm.io/builder" 133 134 sql, args, _ := ToSQL(Expr("a = ? ", 1)) 135 // a = ? [1] 136 sql, args, _ := ToSQL(Eq{"a": Expr("select id from table where c = ?", 1)}) 137 // a=(select id from table where c = ?) [1] 138 ``` 139 140 * `In` and `NotIn` 141 142 ```Go 143 import . "xorm.io/builder" 144 145 sql, args, _ := ToSQL(In("a", 1, 2, 3)) 146 // a IN (?,?,?) [1,2,3] 147 sql, args, _ := ToSQL(In("a", []int{1, 2, 3})) 148 // a IN (?,?,?) [1,2,3] 149 sql, args, _ := ToSQL(In("a", Expr("select id from b where c = ?", 1)))) 150 // a IN (select id from b where c = ?) [1] 151 ``` 152 153 * `IsNull` and `NotNull` 154 155 ```Go 156 import . "xorm.io/builder" 157 158 sql, args, _ := ToSQL(IsNull{"a"}) 159 // a IS NULL [] 160 sql, args, _ := ToSQL(NotNull{"b"}) 161 // b IS NOT NULL [] 162 ``` 163 164 * `And(conds ...Cond)`, And can connect one or more condtions via And 165 166 ```Go 167 import . "xorm.io/builder" 168 169 sql, args, _ := ToSQL(And(Eq{"a":1}, Like{"b", "c"}, Neq{"d", 2})) 170 // a=? AND b LIKE ? AND d<>? [1, %c%, 2] 171 ``` 172 173 * `Or(conds ...Cond)`, Or can connect one or more conditions via Or 174 175 ```Go 176 import . "xorm.io/builder" 177 178 sql, args, _ := ToSQL(Or(Eq{"a":1}, Like{"b", "c"}, Neq{"d", 2})) 179 // a=? OR b LIKE ? OR d<>? [1, %c%, 2] 180 sql, args, _ := ToSQL(Or(Eq{"a":1}, And(Like{"b", "c"}, Neq{"d", 2}))) 181 // a=? OR (b LIKE ? AND d<>?) [1, %c%, 2] 182 ``` 183 184 * `Between` 185 186 ```Go 187 import . "xorm.io/builder" 188 189 sql, args, _ := ToSQL(Between{"a", 1, 2}) 190 // a BETWEEN 1 AND 2 191 ``` 192 193 * Define yourself conditions 194 195 Since `Cond` is an interface. 196 197 ```Go 198 type Cond interface { 199 WriteTo(Writer) error 200 And(...Cond) Cond 201 Or(...Cond) Cond 202 IsValid() bool 203 } 204 ``` 205 206 You can define yourself conditions and compose with other `Cond`.