yiisoft /
db
| Conditions | 1 |
| Paths | 1 |
| Total Lines | 304 |
| Code Lines | 173 |
| Lines | 0 |
| Ratio | 0 % |
| Changes | 7 | ||
| Bugs | 0 | Features | 1 |
Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.
For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.
Commonly applied refactorings include:
If many parameters/temporary variables are present:
| 1 | <?php |
||
| 15 | public function buildConditions(): array |
||
| 16 | { |
||
| 17 | $mock = new Mock(); |
||
| 18 | |||
| 19 | return [ |
||
| 20 | /* empty values */ |
||
| 21 | [['like', 'name', []], '0=1', []], |
||
| 22 | [['not like', 'name', []], '', []], |
||
| 23 | [['or like', 'name', []], '0=1', []], |
||
| 24 | [['or not like', 'name', []], '', []], |
||
| 25 | |||
| 26 | /* not */ |
||
| 27 | [['not', ''], '', []], |
||
| 28 | [['not', 'name'], 'NOT (name)', []], |
||
| 29 | [ |
||
| 30 | [ |
||
| 31 | 'not', |
||
| 32 | $mock->query()->select('exists')->from('some_table'), |
||
| 33 | ], |
||
| 34 | 'NOT ((SELECT [[exists]] FROM [[some_table]]))', [], |
||
| 35 | ], |
||
| 36 | |||
| 37 | /* and */ |
||
| 38 | [['and', '', ''], '', []], |
||
| 39 | [['and', '', 'id=2'], 'id=2', []], |
||
| 40 | [['and', 'id=1', 'id=2'], '(id=1) AND (id=2)', []], |
||
| 41 | [['and', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) AND ((id=1) OR (id=2))', []], |
||
| 42 | [['and', 'id=1', new Expression('id=:qp0', [':qp0' => 2])], '(id=1) AND (id=:qp0)', [':qp0' => 2]], |
||
| 43 | [ |
||
| 44 | [ |
||
| 45 | 'and', |
||
| 46 | ['expired' => false], |
||
| 47 | $mock->query()->select('count(*) > 1')->from('queue'), |
||
| 48 | ], |
||
| 49 | '([[expired]]=:qp0) AND ((SELECT count(*) > 1 FROM [[queue]]))', |
||
| 50 | [':qp0' => false], |
||
| 51 | ], |
||
| 52 | |||
| 53 | /* or */ |
||
| 54 | [['or', 'id=1', 'id=2'], '(id=1) OR (id=2)', []], |
||
| 55 | [['or', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) OR ((id=1) OR (id=2))', []], |
||
| 56 | [['or', 'type=1', new Expression('id=:qp0', [':qp0' => 1])], '(type=1) OR (id=:qp0)', [':qp0' => 1]], |
||
| 57 | |||
| 58 | /* between */ |
||
| 59 | [['between', 'id', 1, 10], '[[id]] BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]], |
||
| 60 | [['not between', 'id', 1, 10], '[[id]] NOT BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]], |
||
| 61 | [ |
||
| 62 | ['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')], |
||
| 63 | '[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()', |
||
| 64 | [], |
||
| 65 | ], |
||
| 66 | [ |
||
| 67 | ['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123], |
||
| 68 | '[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0', |
||
| 69 | [':qp0' => 123], |
||
| 70 | ], |
||
| 71 | [ |
||
| 72 | ['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')], |
||
| 73 | '[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()', |
||
| 74 | [], |
||
| 75 | ], |
||
| 76 | [ |
||
| 77 | ['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123], |
||
| 78 | '[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0', |
||
| 79 | [':qp0' => 123], |
||
| 80 | ], |
||
| 81 | [ |
||
| 82 | new BetweenColumnsCondition('2018-02-11', 'BETWEEN', 'create_time', 'update_time'), |
||
| 83 | ':qp0 BETWEEN [[create_time]] AND [[update_time]]', |
||
| 84 | [':qp0' => '2018-02-11'], |
||
| 85 | ], |
||
| 86 | [ |
||
| 87 | new BetweenColumnsCondition('2018-02-11', 'NOT BETWEEN', 'NOW()', 'update_time'), |
||
| 88 | ':qp0 NOT BETWEEN NOW() AND [[update_time]]', |
||
| 89 | [':qp0' => '2018-02-11'], |
||
| 90 | ], |
||
| 91 | [ |
||
| 92 | new BetweenColumnsCondition(new Expression('NOW()'), 'BETWEEN', 'create_time', 'update_time'), |
||
| 93 | 'NOW() BETWEEN [[create_time]] AND [[update_time]]', |
||
| 94 | [], |
||
| 95 | ], |
||
| 96 | [ |
||
| 97 | new BetweenColumnsCondition(new Expression('NOW()'), 'NOT BETWEEN', 'create_time', 'update_time'), |
||
| 98 | 'NOW() NOT BETWEEN [[create_time]] AND [[update_time]]', |
||
| 99 | [], |
||
| 100 | ], |
||
| 101 | [ |
||
| 102 | new BetweenColumnsCondition( |
||
| 103 | new Expression('NOW()'), |
||
| 104 | 'NOT BETWEEN', |
||
| 105 | $mock->query()->select('min_date')->from('some_table'), |
||
| 106 | 'max_date' |
||
| 107 | ), |
||
| 108 | 'NOW() NOT BETWEEN (SELECT [[min_date]] FROM [[some_table]]) AND [[max_date]]', |
||
| 109 | [], |
||
| 110 | ], |
||
| 111 | [ |
||
| 112 | new BetweenColumnsCondition( |
||
| 113 | new Expression('NOW()'), |
||
| 114 | 'NOT BETWEEN', |
||
| 115 | new Expression('min_date'), |
||
| 116 | $mock->query()->select('max_date')->from('some_table'), |
||
| 117 | ), |
||
| 118 | 'NOW() NOT BETWEEN min_date AND (SELECT [[max_date]] FROM [[some_table]])', |
||
| 119 | [], |
||
| 120 | ], |
||
| 121 | |||
| 122 | /* in */ |
||
| 123 | [ |
||
| 124 | ['in', 'id', [1, 2, $mock->query()->select('three')->from('digits')]], |
||
| 125 | '[[id]] IN (:qp0, :qp1, (SELECT [[three]] FROM [[digits]]))', |
||
| 126 | [':qp0' => 1, ':qp1' => 2], |
||
| 127 | ], |
||
| 128 | [ |
||
| 129 | ['not in', 'id', [1, 2, 3]], |
||
| 130 | '[[id]] NOT IN (:qp0, :qp1, :qp2)', |
||
| 131 | [':qp0' => 1, ':qp1' => 2, ':qp2' => 3], |
||
| 132 | ], |
||
| 133 | [ |
||
| 134 | ['in', 'id', $mock->query()->select('id')->from('users')->where(['active' => 1])], |
||
| 135 | '[[id]] IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', |
||
| 136 | [':qp0' => 1], |
||
| 137 | ], |
||
| 138 | [ |
||
| 139 | ['not in', 'id', $mock->query()->select('id')->from('users')->where(['active' => 1])], |
||
| 140 | '[[id]] NOT IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', |
||
| 141 | [':qp0' => 1], |
||
| 142 | ], |
||
| 143 | [['in', 'id', 1], '[[id]]=:qp0', [':qp0' => 1]], |
||
| 144 | [['in', 'id', [1]], '[[id]]=:qp0', [':qp0' => 1]], |
||
| 145 | [['in', 'id', new TraversableObject([1])], '[[id]]=:qp0', [':qp0' => 1]], |
||
| 146 | 'composite in' => [ |
||
| 147 | ['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]], |
||
| 148 | '([[id]], [[name]]) IN ((:qp0, :qp1))', |
||
| 149 | [':qp0' => 1, ':qp1' => 'oy'], |
||
| 150 | ], |
||
| 151 | 'composite in (just one column)' => [ |
||
| 152 | ['in', ['id'], [['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]], |
||
| 153 | '[[id]] IN (:qp0, :qp1)', |
||
| 154 | [':qp0' => 1, ':qp1' => 2], |
||
| 155 | ], |
||
| 156 | 'composite in using array objects (just one column)' => [ |
||
| 157 | [ |
||
| 158 | 'in', |
||
| 159 | new TraversableObject(['id']), |
||
| 160 | new TraversableObject([['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]), |
||
| 161 | ], |
||
| 162 | '[[id]] IN (:qp0, :qp1)', |
||
| 163 | [':qp0' => 1, ':qp1' => 2], |
||
| 164 | ], |
||
| 165 | |||
| 166 | /* in using array objects. */ |
||
| 167 | [['id' => new TraversableObject([1, 2])], '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]], |
||
| 168 | [ |
||
| 169 | ['in', 'id', new TraversableObject([1, 2, 3])], |
||
| 170 | '[[id]] IN (:qp0, :qp1, :qp2)', |
||
| 171 | [':qp0' => 1, ':qp1' => 2, ':qp2' => 3], |
||
| 172 | ], |
||
| 173 | |||
| 174 | /* in using array objects containing null value */ |
||
| 175 | [['in', 'id', new TraversableObject([1, null])], '[[id]]=:qp0 OR [[id]] IS NULL', [':qp0' => 1]], |
||
| 176 | [ |
||
| 177 | ['in', 'id', new TraversableObject([1, 2, null])], |
||
| 178 | '[[id]] IN (:qp0, :qp1) OR [[id]] IS NULL', [':qp0' => 1, ':qp1' => 2], |
||
| 179 | ], |
||
| 180 | |||
| 181 | /* not in using array object containing null value */ |
||
| 182 | [ |
||
| 183 | ['not in', 'id', new TraversableObject([1, null])], |
||
| 184 | '[[id]]<>:qp0 AND [[id]] IS NOT NULL', [':qp0' => 1], |
||
| 185 | ], |
||
| 186 | [ |
||
| 187 | ['not in', 'id', new TraversableObject([1, 2, null])], |
||
| 188 | '[[id]] NOT IN (:qp0, :qp1) AND [[id]] IS NOT NULL', |
||
| 189 | [':qp0' => 1, ':qp1' => 2], |
||
| 190 | ], |
||
| 191 | |||
| 192 | /* in using array object containing only null value */ |
||
| 193 | [['in', 'id', new TraversableObject([null])], '[[id]] IS NULL', []], |
||
| 194 | [['not in', 'id', new TraversableObject([null])], '[[id]] IS NOT NULL', []], |
||
| 195 | 'composite in using array objects' => [ |
||
| 196 | [ |
||
| 197 | 'in', |
||
| 198 | new TraversableObject(['id', 'name']), |
||
| 199 | new TraversableObject([['id' => 1, 'name' => 'oy'], ['id' => 2, 'name' => 'yo']]), |
||
| 200 | ], |
||
| 201 | '([[id]], [[name]]) IN ((:qp0, :qp1), (:qp2, :qp3))', |
||
| 202 | [':qp0' => 1, ':qp1' => 'oy', ':qp2' => 2, ':qp3' => 'yo'], |
||
| 203 | ], |
||
| 204 | |||
| 205 | /* in object conditions */ |
||
| 206 | [new InCondition('id', 'in', 1), '[[id]]=:qp0', [':qp0' => 1]], |
||
| 207 | [new InCondition('id', 'in', [1]), '[[id]]=:qp0', [':qp0' => 1]], |
||
| 208 | [new InCondition('id', 'not in', 1), '[[id]]<>:qp0', [':qp0' => 1]], |
||
| 209 | [new InCondition('id', 'not in', [1]), '[[id]]<>:qp0', [':qp0' => 1]], |
||
| 210 | [new InCondition('id', 'in', [1, 2]), '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]], |
||
| 211 | [new InCondition('id', 'not in', [1, 2]), '[[id]] NOT IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]], |
||
| 212 | [new InCondition([], 'in', 1), '0=1', []], |
||
| 213 | [new InCondition([], 'in', [1]), '0=1', []], |
||
| 214 | [new InCondition(['id', 'name'], 'in', []), '0=1', []], |
||
| 215 | [ |
||
| 216 | new InCondition(['id'], 'in', $mock->query()->select('id')->from('users')->where(['active' => 1])), |
||
| 217 | '([[id]]) IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', |
||
| 218 | [':qp0' => 1], |
||
| 219 | ], |
||
| 220 | [ |
||
| 221 | new InCondition(['id', 'name'], 'in', [['id' => 1]]), |
||
| 222 | '([[id]], [[name]]) IN ((:qp0, NULL))', |
||
| 223 | [':qp0' => 1], |
||
| 224 | ], |
||
| 225 | [ |
||
| 226 | new InCondition(['id', 'name'], 'in', [['name' => 'oy']]), |
||
| 227 | '([[id]], [[name]]) IN ((NULL, :qp0))', |
||
| 228 | [':qp0' => 'oy'], |
||
| 229 | ], |
||
| 230 | [ |
||
| 231 | new InCondition(['id', 'name'], 'in', [['id' => 1, 'name' => 'oy']]), |
||
| 232 | '([[id]], [[name]]) IN ((:qp0, :qp1))', |
||
| 233 | [':qp0' => 1, ':qp1' => 'oy'], |
||
| 234 | ], |
||
| 235 | |||
| 236 | /* exists */ |
||
| 237 | [ |
||
| 238 | [ |
||
| 239 | 'exists', |
||
| 240 | $mock->query()->select('id')->from('users')->where(['active' => 1]), |
||
| 241 | ], |
||
| 242 | 'EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', |
||
| 243 | [':qp0' => 1], |
||
| 244 | ], |
||
| 245 | [ |
||
| 246 | ['not exists', $mock->query()->select('id')->from('users')->where(['active' => 1])], |
||
| 247 | 'NOT EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', [':qp0' => 1], |
||
| 248 | ], |
||
| 249 | |||
| 250 | /* simple conditions */ |
||
| 251 | [['=', 'a', 'b'], '[[a]] = :qp0', [':qp0' => 'b']], |
||
| 252 | [['>', 'a', 1], '[[a]] > :qp0', [':qp0' => 1]], |
||
| 253 | [['>=', 'a', 'b'], '[[a]] >= :qp0', [':qp0' => 'b']], |
||
| 254 | [['<', 'a', 2], '[[a]] < :qp0', [':qp0' => 2]], |
||
| 255 | [['<=', 'a', 'b'], '[[a]] <= :qp0', [':qp0' => 'b']], |
||
| 256 | [['<>', 'a', 3], '[[a]] <> :qp0', [':qp0' => 3]], |
||
| 257 | [['!=', 'a', 'b'], '[[a]] != :qp0', [':qp0' => 'b']], |
||
| 258 | [ |
||
| 259 | ['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL 1 MONTH)')], |
||
| 260 | '[[date]] >= DATE_SUB(NOW(), INTERVAL 1 MONTH)', |
||
| 261 | [], |
||
| 262 | ], |
||
| 263 | [ |
||
| 264 | ['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL :month MONTH)', [':month' => 2])], |
||
| 265 | '[[date]] >= DATE_SUB(NOW(), INTERVAL :month MONTH)', |
||
| 266 | [':month' => 2], |
||
| 267 | ], |
||
| 268 | [ |
||
| 269 | ['=', 'date', $mock->query()->select('max(date)')->from('test')->where(['id' => 5])], |
||
| 270 | '[[date]] = (SELECT max(date) FROM [[test]] WHERE [[id]]=:qp0)', |
||
| 271 | [':qp0' => 5], |
||
| 272 | ], |
||
| 273 | [['=', 'a', null], '[[a]] = NULL', []], |
||
| 274 | |||
| 275 | /* operand1 is Expression */ |
||
| 276 | [ |
||
| 277 | ['=', new Expression('date'), '2019-08-01'], |
||
| 278 | 'date = :qp0', |
||
| 279 | [':qp0' => '2019-08-01'], |
||
| 280 | ], |
||
| 281 | [ |
||
| 282 | ['=', $mock->query()->select('COUNT(*)')->from('test')->where(['id' => 6]), 0], |
||
| 283 | '(SELECT COUNT(*) FROM [[test]] WHERE [[id]]=:qp0) = :qp1', |
||
| 284 | [':qp0' => 6, ':qp1' => 0], |
||
| 285 | ], |
||
| 286 | |||
| 287 | /* hash condition */ |
||
| 288 | [['a' => 1, 'b' => 2], '([[a]]=:qp0) AND ([[b]]=:qp1)', [':qp0' => 1, ':qp1' => 2]], |
||
| 289 | [ |
||
| 290 | ['a' => new Expression('CONCAT(col1, col2)'), 'b' => 2], |
||
| 291 | '([[a]]=CONCAT(col1, col2)) AND ([[b]]=:qp0)', |
||
| 292 | [':qp0' => 2], |
||
| 293 | ], |
||
| 294 | [['a' => null], '[[a]] IS NULL', []], |
||
| 295 | |||
| 296 | /* direct conditions */ |
||
| 297 | ['a = CONCAT(col1, col2)', 'a = CONCAT(col1, col2)', []], |
||
| 298 | [ |
||
| 299 | new Expression('a = CONCAT(col1, :param1)', ['param1' => 'value1']), |
||
| 300 | 'a = CONCAT(col1, :param1)', |
||
| 301 | ['param1' => 'value1'], |
||
| 302 | ], |
||
| 303 | |||
| 304 | /* Expression with params as operand of 'not' */ |
||
| 305 | [ |
||
| 306 | ['not', new Expression('any_expression(:a)', [':a' => 1])], |
||
| 307 | 'NOT (any_expression(:a))', [':a' => 1], |
||
| 308 | ], |
||
| 309 | [new Expression('NOT (any_expression(:a))', [':a' => 1]), 'NOT (any_expression(:a))', [':a' => 1]], |
||
| 310 | |||
| 311 | /* like */ |
||
| 312 | [['like', 'a', 'b'], '[[a]] LIKE :qp0', [':qp0' => '%b%']], |
||
| 313 | [ |
||
| 314 | ['like', 'a', new Expression(':qp0', [':qp0' => '%b%'])], |
||
| 315 | '[[a]] LIKE :qp0', |
||
| 316 | [':qp0' => '%b%'], |
||
| 317 | ], |
||
| 318 | [['like', new Expression('CONCAT(col1, col2)'), 'b'], 'CONCAT(col1, col2) LIKE :qp0', [':qp0' => '%b%']], |
||
| 319 | ]; |
||
| 322 |
