github.com/artisanhe/tools@v1.0.1-0.20210607022958-19a8fef2eb04/sqlx/builder/condition_test.go (about) 1 package builder 2 3 import ( 4 "testing" 5 ) 6 7 func TestBuilderCond(t *testing.T) { 8 table := T(DB("db"), "t") 9 10 exprCases{ 11 Case("CondRules", 12 NewCondRules(). 13 When(true, Col(table, "a").Eq(1)). 14 When(true, Col(table, "b").Like(`g`)). 15 When(false, Col(table, "b").Like(`g`)). 16 ToCond(), 17 Expr( 18 "(`a` = ?) AND (`b` LIKE ?)", 19 1, "%g%", 20 ), 21 ), 22 Case( 23 "Chain Condition", 24 Col(table, "a").Eq(1). 25 And(Col(table, "b").LeftLike("c")). 26 Or(Col(table, "a").Eq(2)). 27 Xor(Col(table, "b").RightLike("g")).Expr(), 28 Expr( 29 "(((`a` = ?) AND (`b` LIKE ?)) OR (`a` = ?)) XOR (`b` LIKE ?)", 30 1, "%c", 2, "g%", 31 ), 32 ), 33 Case( 34 "Compose Condition", 35 Xor( 36 Or( 37 And( 38 Col(table, "a").Eq(1), 39 Col(table, "b").Like("c"), 40 ), 41 Col(table, "a").Eq(2), 42 ), 43 Col(table, "b").Like("g"), 44 ).Expr(), 45 Expr( 46 "(((`a` = ?) AND (`b` LIKE ?)) OR (`a` = ?)) XOR (`b` LIKE ?)", 47 1, "%c%", 2, "%g%", 48 ), 49 ), 50 Case( 51 "Skip nil", 52 Xor( 53 Col(table, "a").In(), 54 Or( 55 Col(table, "a").NotIn(), 56 And( 57 nil, 58 Col(table, "a").Eq(1), 59 Col(table, "b").Like("c"), 60 ), 61 Col(table, "a").Eq(2), 62 ), 63 Col(table, "b").Like("g"), 64 ).Expr(), 65 Expr( 66 "(((`a` = ?) AND (`b` LIKE ?)) OR (`a` = ?)) XOR (`b` LIKE ?)", 67 1, "%c%", 2, "%g%", 68 ), 69 ), 70 Case( 71 "XOR", 72 Xor( 73 Col(table, "a").In(), 74 Or( 75 Col(table, "a").NotIn(), 76 And( 77 nil, 78 Col(table, "a").Eq(1), 79 Col(table, "b").Like("c"), 80 ), 81 Col(table, "a").Eq(2), 82 ), 83 Col(table, "b").Like("g"), 84 ).Expr(), 85 Expr( 86 "(((`a` = ?) AND (`b` LIKE ?)) OR (`a` = ?)) XOR (`b` LIKE ?)", 87 1, "%c%", 2, "%g%", 88 ), 89 ), 90 Case( 91 "XOR", 92 Xor( 93 Col(table, "a").Eq(1), 94 Col(table, "b").Like("g"), 95 ).Expr(), 96 Expr( 97 "(`a` = ?) XOR (`b` LIKE ?)", 98 1, "%g%", 99 ), 100 ), 101 Case( 102 "Like", 103 Col(table, "d").Like("e").Expr(), 104 Expr( 105 "`d` LIKE ?", 106 "%e%", 107 ), 108 ), 109 Case( 110 "Not like", 111 Col(table, "d").NotLike("e").Expr(), 112 Expr( 113 "`d` NOT LIKE ?", 114 "%e%", 115 ), 116 ), 117 Case( 118 "Equal", 119 Col(table, "d").Eq("e").Expr(), 120 Expr( 121 "`d` = ?", 122 "e", 123 ), 124 ), 125 Case( 126 "Not Equal", 127 Col(table, "d").Neq("e").Expr(), 128 Expr( 129 "`d` <> ?", 130 "e", 131 ), 132 ), 133 Case( 134 "In", 135 Col(table, "d").In("e", "f").Expr(), 136 Expr( 137 "`d` IN (?,?)", 138 "e", "f", 139 ), 140 ), 141 Case( 142 "In With Select", 143 Col(table, "d").In(SelectFrom(table).Where(Col(table, "d").Eq(1))).Expr(), 144 Expr( 145 "`d` IN (SELECT * FROM `db`.`t` WHERE `d` = ?)", 146 1, 147 ), 148 ), 149 Case( 150 "NotIn", 151 Col(table, "d").NotIn("e", "f").Expr(), 152 Expr( 153 "`d` NOT IN (?,?)", 154 "e", "f", 155 ), 156 ), 157 Case( 158 "Not In With Select", 159 Col(table, "d").NotIn(SelectFrom(table).Where(Col(table, "d").Eq(1))).Expr(), 160 Expr( 161 "`d` NOT IN (SELECT * FROM `db`.`t` WHERE `d` = ?)", 162 1, 163 ), 164 ), 165 Case( 166 "Less than", 167 Col(table, "d").Lt(3).Expr(), 168 Expr( 169 "`d` < ?", 170 3, 171 ), 172 ), 173 Case( 174 "Less or equal than", 175 Col(table, "d").Lte(3).Expr(), 176 Expr( 177 "`d` <= ?", 178 3, 179 ), 180 ), 181 Case( 182 "Greater than", 183 Col(table, "d").Gt(3).Expr(), 184 Expr( 185 "`d` > ?", 186 3, 187 ), 188 ), 189 Case( 190 "Greater or equal than", 191 Col(table, "d").Gte(3).Expr(), 192 Expr( 193 "`d` >= ?", 194 3, 195 ), 196 ), 197 Case( 198 "Between", 199 Col(table, "d").Between(0, 2).Expr(), 200 Expr( 201 "`d` BETWEEN ? AND ?", 202 0, 2, 203 ), 204 ), 205 Case( 206 "Not between", 207 Col(table, "d").NotBetween(0, 2).Expr(), 208 Expr( 209 "`d` NOT BETWEEN ? AND ?", 210 0, 2, 211 ), 212 ), 213 Case( 214 "Is null", 215 Col(table, "d").IsNull().Expr(), 216 Expr( 217 "`d` IS NULL", 218 ), 219 ), 220 Case( 221 "Is not null", 222 Col(table, "d").IsNotNull().Expr(), 223 Expr( 224 "`d` IS NOT NULL", 225 ), 226 ), 227 }.Run(t, "Condition") 228 }